Hello > > Also try this: > > ALTER DEFAULT PRIVILEGES FOR ex_mainuser GRANT INSERT, SELECT, UPDATE, > > DELETE, TRUNCATE, REFERENCES, TRIGGER > > ON TABLES TO ex_dbuser; > > > > You execute the ALTER DEFAULT PRIVILEGES as su, so the grant applies to > > objects created by su and not > > ex_mainuser, unless you specify it with FOR ex_mainuser. > > > > So... I repeated the test. > > --- login with postgres: > > CREATE DATABASE db_testrole > WITH ENCODING='UTF8' > TEMPLATE=template0 > CONNECTION LIMIT=-1; > > CREATE ROLE u_tr_db LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE > NOREPLICATION; > > > CREATE ROLE u_tr_main LOGIN > NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; > GRANT u_tr_db TO u_tr_main; > > > ALTER DATABASE db_testrole > OWNER TO u_tr_db; > > REVOKE ALL ON DATABASE db_testrole FROM public; > GRANT CREATE, TEMPORARY ON DATABASE db_testrole TO public; > GRANT ALL ON DATABASE db_testrole TO u_tr_db; > > ALTER DEFAULT PRIVILEGES > GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER > ON TABLES > TO u_tr_db;
Here you are telling PostgreSQL to grant those privileges to u_tr_db on tables created by user postgres. > ---- login with u_tr_main: > > create table t_canyouseeme_1 (k int); > > ---- login with u_tr_db: > > select * from t_canyouseeme_1; > > ERROR: permission denied for relation t_canyouseeme_1 > SQL state: 42501 > As you see before, u_tr_db got all default privileges on future tables, so I > don't understand why he don't get to > "t_canyouseeme_1". This is not correct. You issued the ALTER DEFAULT PRIVILEGES statement as user postgres. So u_tr_db is granted privileges only on tables created by user postgres. Since you created the table as user u_tr_main the default privileges don't apply, because there are none defined. > If I try to use these things they would work: > > A.) > > ---- login with u_tr_main: > > set role u_tr_db; > > create table t_canyouseeme_2 (k int); > > ---- login with u_tr_db: > > select * from t_canyouseeme_2; -- OK! Yes, because the owner of the table is u_tr_db. With set role user u_tr_main is impersonating user u_tr_db. > B.) > > ---- login with su: > > > ALTER DEFAULT PRIVILEGES FOR role u_tr_main GRANT INSERT, > SELECT, UPDATE, DELETE, TRUNCATE, > REFERENCES, TRIGGER ON TABLES TO u_tr_db; Here you are telling PostgreSQL to grant privileges on tables created by u_tr_main to u_tr_db. > ---- login with u_tr_main: > > create table t_canyouseeme_3 (k int); > > ---- login with u_tr_db: > > select * from t_canyouseeme_3; -- OK! > > > A.) is because I can set role to u_tr_db and then he is the creator, he get > all rights. > B.) I don't understand this statement... :-( :-( :-( > > So the main questions. > Why the default privilege settings aren't affected on newly created table? > See: > > ALTER DEFAULT PRIVILEGES > GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER > ON TABLES > TO u_tr_db; They do if the user creating the table is the user that issued the statement. In the case above postgres. > What are the meaning of this statement if they won't usable for object > created by another users? > U_TR_DB is owner, so they have all privileges for next tables he will create. > So I supposed that "default privileges" is for future objects created by > different users. > But this not works here. > > I don't understand case B. > U_TR_MAIN gives all privileges to U_TR_DB for all newly created table? Yes. You may also choose to restrict the privileges, instead of granting all of them. > What are the differences between? > > 1. ALTER DEFAULT PRIVILEGES > GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER > ON TABLES > TO u_tr_db; > 2. ALTER DEFAULT PRIVILEGES FOR role u_tr_main GRANT INSERT, SELECT, > UPDATE, DELETE, TRUNCATE, REFERENCES, > TRIGGER ON TABLES TO u_tr_db; In 1 the rule apply for tables created by the user that created the default privileges. Specifically the current_user is the one used for authorization checks. In 2 you say explicitly that the rule applies to tables created by user u_tr_main. > Why the second works and first not? They both work. In the first statement it works if you create tables as the user who was the current_user when you issued the alter default privileges statement. In the second it works if you create a table as user u_tr_main. > --- > > > db_testrole-# \ddp > Default access privileges > Owner | Schema | Type | Access privileges > -----------+--------+-------+----------------------------- > postgres | | table | postgres=arwdDxt/postgres + > | | | u_tr_db=arwdDxt/postgres > u_tr_main | | table | u_tr_db=arwdDxt/u_tr_main + > | | | u_tr_main=arwdDxt/u_tr_main > (2 rows) Here you see in different form what I already mentioned above. Bye Charles > > db_testrole-# \d > List of relations > Schema | Name | Type | Owner > --------+-----------------+-------+----------- > public | t_canyouseeme_1 | table | u_tr_main > public | t_canyouseeme_2 | table | u_tr_db > public | t_canyouseeme_3 | table | u_tr_main > (3 rows) > > > --- > > > > Thank you for your help! > > Best wishes > dd > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
