Thread: Copy user privileges
Hi All, I have dumped a database with no data and restored it with a new db name. I want to keep all the privileges but assign them to a new user. What is the best way of doing this? Is it to alter the system tables directly in which case which ones. TIA, Graham
"Graham Vickrage" <graham@gpmd.co.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.
regards, tom lane
On Tue, Jul 12, 2005 at 03:12:50PM -0400, Tom Lane wrote:
> "Graham Vickrage" <graham@gpmd.co.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/
Thanks for the suggestions but as you guessed I do need to keep the old
user and also groups would be unsuitable as I don't want either user to
be able to see data on the other database.
Thought there would be some SQL statement I could run on the sys tables
but I don't know enough about the internals to attempt it.
Thanks again,
Graham
-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: 12 July 2005 20:29
To: Tom Lane
Cc: Graham Vickrage; pgsql-sql@postgresql.org
Subject: Re: [SQL] Copy user privileges
On Tue, Jul 12, 2005 at 03:12:50PM -0400, Tom Lane wrote:
> "Graham Vickrage" <graham@gpmd.co.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/