Re: Clearing old user ids completely

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