pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCopy user privileges
4+ messages / 3 participants
[nested] [flat]
* Copy user privileges
@ 2005-07-12 12:21 Graham Vickrage <graham@gpmd.co.uk>
2005-07-12 19:12 ` Re: Copy user privileges Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Graham Vickrage @ 2005-07-12 12:21 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Copy user privileges
2005-07-12 12:21 Copy user privileges Graham Vickrage <graham@gpmd.co.uk>
@ 2005-07-12 19:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-12 19:29 ` Re: Copy user privileges Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2005-07-12 19:12 UTC (permalink / raw)
To: Graham Vickrage <graham@gpmd.co.uk>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Copy user privileges
2005-07-12 12:21 Copy user privileges Graham Vickrage <graham@gpmd.co.uk>
2005-07-12 19:12 ` Re: Copy user privileges Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-07-12 19:29 ` Michael Fuhr <mike@fuhr.org>
2005-07-13 11:31 ` Re: Copy user privileges Graham Vickrage <graham@gpmd.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2005-07-12 19:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Graham Vickrage <graham@gpmd.co.uk>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Copy user privileges
2005-07-12 12:21 Copy user privileges Graham Vickrage <graham@gpmd.co.uk>
2005-07-12 19:12 ` Re: Copy user privileges Tom Lane <tgl@sss.pgh.pa.us>
2005-07-12 19:29 ` Re: Copy user privileges Michael Fuhr <mike@fuhr.org>
@ 2005-07-13 11:31 ` Graham Vickrage <graham@gpmd.co.uk>
0 siblings, 0 replies; 4+ messages in thread
From: Graham Vickrage @ 2005-07-13 11:31 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-07-13 11:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-12 12:21 Copy user privileges Graham Vickrage <graham@gpmd.co.uk>
2005-07-12 19:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-12 19:29 ` Michael Fuhr <mike@fuhr.org>
2005-07-13 11:31 ` Graham Vickrage <graham@gpmd.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox