Re: Copy user privileges

On Tue, Jul 12, 2005 at 03:12:50PM -0400, Tom Lane wrote:
> "Graham Vickrage" <graham(at)gpmd(dot)co(dot)uk> writes:
> > I want to keep all the privileges but assign them to a new user.
>
> > What is the best way of doing this?
>
> How about just renaming the old user to a new name?
>
> I don't think we have an ALTER command for that, but an UPDATE on
> pg_shadow would get the job done just as well.

What about ALTER USER RENAME TO?

test=# CREATE USER user1;
CREATE USER
test=# CREATE TABLE foo (x integer);
CREATE TABLE
test=# GRANT SELECT ON foo TO user1;
GRANT
test=# \z foo
Access privileges for database "test"
Schema | Name | Type | Access privileges
--------+------+-------+----------------------------------------------
public | foo | table | {postgres=arwdRxt/postgres,user1=r/postgres}
(1 row)

test=# ALTER USER user1 RENAME TO user2;
ALTER USER
test=# \z foo
Access privileges for database "test"
Schema | Name | Type | Access privileges
--------+------+-------+----------------------------------------------
public | foo | table | {postgres=arwdRxt/postgres,user2=r/postgres}
(1 row)

Renaming the user is only useful if you no longer need the old user.
If you need to keep the old user and copy its privileges, then consider
granting privileges to groups instead of to users -- then you could
just add the new user to a group.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/