Обсуждение: Clearing old user ids completely
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. Thanks. -- Justin Pasher
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. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
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. Justin Pasher
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. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
Erik Jones <erik@myemma.com> writes:
> 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.
Yeah, getting rid of references to the user in ACL lists is going to
be the main pain-in-the-neck here. Ownership is relatively easy to fix
with direct updates on the catalog owner columns (and in 7.4 there's
by definition nothing behind-the-scenes in dependency tables). I
can't think of any equally easy fix for ACL references though, because
the available SQL-level operations on aclitem arrays are pretty weak.
regards, tom lane
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