default privileges
in [PgSql]
|
From: Tom Lane on 3 Apr 2010 17:16 Jaime Casanova <jcasanov(a)systemguards.com.ec> writes: > I create some default privileges, now i'm trying to drop those roles > but i get this error: > mic=# drop role jcm; > ERROR: role "jcm" cannot be dropped because some objects depend on it > DETAIL: owner of default privileges on new relations belonging to > role jcm in schema public > So i look for default privileges and try to REVOKE privileges for all > roles i found here > mic=# \ddp > Default access privileges > Owner | Schema | Type | Access privileges > ----------+--------+-------+------------------- > jcm | public | table | > jcm1 | public | table | > postgres | public | table | > rup | public | table | > (4 rows) Yeah. The problem here is that once you've created an entry in pg_default_acl, there is no way to make it go away. You can reduce it to an empty ACL list, as Jaime evidently did, but the entry is still there and still has a dependency on the owner. This clearly wasn't thought out well enough :-(. I suggest that what we should do is arrange for the entry to be deleted by an ALTER DEFAULT PRIVILEGES command that restores it to the default state --- that is, empty for a schema-local default ACL, or equal to the built-in privilege defaults for a global default ACL. Then, the way to get rid of it is just to reverse whatever ALTER DEFAULT PRIVILEGES commands you gave originally. Anything else will require inventing special commands or special rules. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 3 Apr 2010 17:27 I wrote: > Yeah. The problem here is that once you've created an entry in > pg_default_acl, there is no way to make it go away. Actually that's not true: you can get rid of it with DROP OWNED BY. This fact is even documented in the ALTER DEFAULT PRIVILEGES manual page: If you wish to drop a role that has had its global default privileges altered, it is necessary to use DROP OWNED BY first, to get rid of the default privileges entry for the role. Not sure if this is good enough or we need to provide some more-obvious way of dealing with it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jaime Casanova on 3 Apr 2010 19:56 On Sat, Apr 3, 2010 at 5:27 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > I wrote: >> Yeah.  The problem here is that once you've created an entry in >> pg_default_acl, there is no way to make it go away. > > Actually that's not true: you can get rid of it with DROP OWNED BY. > This fact is even documented in the ALTER DEFAULT PRIVILEGES manual > page: > >     If you wish to drop a role that has had its global default >     privileges altered, it is necessary to use DROP OWNED BY first, >     to get rid of the default privileges entry for the role. > ah! i obviously didn't read the manual in detail :) > Not sure if this is good enough or we need to provide some more-obvious > way of dealing with it. > it's strange that a REVOKE doesn't clean what a GRANT did, and DROP OWNED BY seems very dangerous (at least if i forgot to make REASSIGN OWNED first). we can let it as it is, but at least we can add a HINT for use DROP OWNED BY having execute REASSIGN OWNED first... or we can make what seems more reasonable, make the REVOKE clean the mess :) if you prefer the second way i can try to prepare a patch -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL AsesorÃa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 4 Apr 2010 21:59 Jaime Casanova <jcasanov(a)systemguards.com.ec> writes: > On Sat, Apr 3, 2010 at 5:27 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >> Not sure if this is good enough or we need to provide some more-obvious >> way of dealing with it. > it's strange that a REVOKE doesn't clean what a GRANT did, and DROP > OWNED BY seems very dangerous (at least if i forgot to make REASSIGN > OWNED first). Agreed --- I fixed it so that granting or revoking back to the default permissions set will remove the entry. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jaime Casanova on 4 Apr 2010 22:34 On Sun, Apr 4, 2010 at 9:59 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Jaime Casanova <jcasanov(a)systemguards.com.ec> writes: >> On Sat, Apr 3, 2010 at 5:27 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >>> Not sure if this is good enough or we need to provide some more-obvious >>> way of dealing with it. > >> it's strange that a REVOKE doesn't clean what a GRANT did, and DROP >> OWNED BY seems very dangerous (at least if i forgot to make REASSIGN >> OWNED first). > > Agreed --- I fixed it so that granting or revoking back to the default > permissions set will remove the entry. > ah! now is working nice... thanks -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL AsesorÃa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
|
Pages: 1 Prev: [HACKERS] default privileges Next: [HACKERS] "pg_stat_tmp/pgstat.stat": Stale NFS file handle |