Re: Clearing old user ids completely
От | Justin Pasher |
---|---|
Тема | Re: Clearing old user ids completely |
Дата | |
Msg-id | 478D47E7.1000008@newmediagateway.com обсуждение исходный текст |
Ответ на | Re: Clearing old user ids completely (Erik Jones <erik@myemma.com>) |
Список | pgsql-general |
Erik Jones wrote: > > On Jan 15, 2008, at 4:53 PM, Justin Pasher wrote: > >> Erik Jones wrote: >>> On Jan 15, 2008, at 3:59 PM, Justin Pasher wrote: >>> >>>> PostgreSQL 7.4.17 >>>> >>>> My situation is basically like the one states in the archives: >>>> >>>> http://archives.postgresql.org/pgsql-sql/2005-10/msg00165.php >>>> >>>> We have some tables that used to be owned by a user (user id 117) >>>> that no longer exists. Because the user no longer exists, when the >>>> database is dumped via pg_dump, it spits out warnings about an >>>> invalid owner. The reason behind all of this is completely >>>> understandable (kind of like a dangling symlink), and the solution >>>> in the archive to get a usable dump is to recreate the user with >>>> the missing ID, then Postgres will no longer complain. >>>> >>>> My question is if there is any way to truly delete the previous >>>> user and fix any associated permissions that may be dangling >>>> around. I've noticed it's possible to update the pg_class table's >>>> relowner column to alter the owner of a table (not sure if that's >>>> really safe, though). However, the relacl column is of type >>>> "aclitem[]", so you can't update it in the same way. Newer versions >>>> of Postgres (8.1) will completely prevent you from deleting the >>>> user if anything is still linked to it, but I'm confused exactly >>>> how to get this older permission information cleared out. >>> >>> Well, you could try, as a superuser, changing the ownership of all >>> of those tables to an existing user and you can do that via ALTER >>> TABLE without having to edit pg_class directly. >> >> Well, yes, that's the way I normally change the user of a table. I >> usually only mess with pg_class if I want to do a mass change on the >> owners of the table without having to mess with building a table list >> separately and creating the individual ALTER TABLE ... OWNER >> commands. My main trouble is just trying to completely get rid of the >> faulty permissions assigned to the table without having to leave the >> previous owner account sitting in the system. > > You can build and EXECUTE the ALTER TABLE commands in a function of a > few lines. With regards to removing the faulty permissions, will > REVOKE not work if the user doesn't exist in the system anymore (I > honestly don't know much about pre-8.0 behaviours)? If not take a > look at the aclitem functions in the pg_catalog schema (in psql: \df > *acl*), they may be what you're looking for. See, that's the catch. Since Postgres uses the table creator's user account as the one for all of the GRANT/REVOKE commands, the user can't revoke access to a table they own (or that Postgres THINKS they own according to the acl). It just ends up leaving it in a messier state. My run through is below. I'll have to look at the various acl related functions to see if any of them can accomplish this. Thanks. template1=# CREATE USER testuser WITH CREATEDB ENCRYPTED PASSWORD 'test'; CREATE USER template1=# \du testuser List of database users User name | User ID | Attributes -----------+---------+----------------- testuser | 128 | create database (1 row) template1=# \c - testuser Password: You are now connected as new user "testuser". template1=> CREATE TABLE test_table (id int); CREATE TABLE template1=> \dp test_table Access privileges for database "template1" Schema | Table | Access privileges --------+------------+------------------- public | test_table | (1 row) template1=> GRANT SELECT on test_table TO postgres; GRANT template1=> \dp test_table Access privileges for database "template1" Schema | Table | Access privileges --------+------------+-------------------------------------------------------- public | test_table | {testuser=a*r*w*d*R*x*t*/testuser,postgres=r/testuser} (1 row) template1=> \c - justinp Password: You are now connected as new user "justinp". template1=# DROP USER testuser; DROP USER template1=# \dp test_table Access privileges for database "template1" Schema | Table | Access privileges --------+------------+----------------------------------------- public | test_table | {128=a*r*w*d*R*x*t*/128,postgres=r/128} (1 row) template1=# REVOKE ALL ON test_table FROM testuser; ERROR: user "testuser" does not exist template1=# CREATE USER testuser WITH CREATEDB ENCRYPTED PASSWORD 'test' SYSID 128; CREATE USER template1=# \dp test_table Access privileges for database "template1" Schema | Table | Access privileges --------+------------+-------------------------------------------------------- public | test_table | {testuser=a*r*w*d*R*x*t*/testuser,postgres=r/testuser} (1 row) template1=# REVOKE ALL ON test_table FROM testuser; REVOKE template1=# \dp test_table Access privileges for database "template1" Schema | Table | Access privileges --------+------------+------------------------------------------------- public | test_table | {testuser=*******/testuser,postgres=r/testuser} (1 row) template1=# DROP USER testuser; DROP USER template1=# \dp test_table Access privileges for database "template1" Schema | Table | Access privileges --------+------------+---------------------------------- public | test_table | {128=*******/128,postgres=r/128} (1 row) template1=# REVOKE ALL ON test_table FROM postgres; REVOKE template1=# \dp test_table Access privileges for database "template1" Schema | Table | Access privileges --------+------------+------------------- public | test_table | {128=*******/128} (1 row) template1=# \dt test_table List of relations Schema | Name | Type | Owner --------+------------+-------+------- public | test_table | table | (1 row) template1=# ALTER TABLE test_table OWNER TO justinp; ALTER TABLE template1=# \dp test_table Access privileges for database "template1" Schema | Table | Access privileges --------+------------+------------------- public | test_table | {128=*******/128} (1 row) Justin Pasher
В списке pgsql-general по дате отправления:
Следующее
От: ClodoaldoДата:
Сообщение: Re: ATTN: Clodaldo was Performance problem. Could it be related to 8.3-beta4?