pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBest practice to create a read-only user?
3+ messages / 3 participants
[nested] [flat]
* Best practice to create a read-only user?
@ 2013-05-03 14:03 matthias ritzkowski <matthias@marlinmobile.com>
0 siblings, 2 replies; 3+ messages in thread
From: matthias ritzkowski @ 2013-05-03 14:03 UTC (permalink / raw)
To: pgsql-admin
Hello,
Usually I would
create user uuu password 'ppp';
GRANT usage on schema zzz to uuu;
GRANT select on all tables in schema zzz to uuu;
But just this morning someone used
create user uuu password 'ppp';
alter user uuu set default_transaction_read_only = on;
GRANT select on all tables in schema zzz to uuu;
So I only added the grant usage and it worked fine.
What do people use day to day?
I had frankly never explored the default_transaction_read_only
parameter ...
--
regards
Matthias Ritzkowski
-marlinmobile-
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best practice to create a read-only user?
@ 2013-05-03 15:05 E. S. <yields.falsehood@gmail.com>
parent: matthias ritzkowski <matthias@marlinmobile.com>
1 sibling, 0 replies; 3+ messages in thread
From: E. S. @ 2013-05-03 15:05 UTC (permalink / raw)
To: matthias ritzkowski <matthias@marlinmobile.com>; +Cc: pgsql-admin
default_transaction_read_only is just a default. Users can still disable it
for themselves and it is not intended to act as a security measure. In the
second example, user uuu could still create and modify data for which s/he
has the privileges granted to do so by first issuing a "set transaction
read write".
On Fri, May 3, 2013 at 10:03 AM, matthias ritzkowski <
matthias@marlinmobile.com> wrote:
> Hello,
>
> Usually I would
>
> create user uuu password 'ppp';
> GRANT usage on schema zzz to uuu;
> GRANT select on all tables in schema zzz to uuu;
>
>
> But just this morning someone used
> create user uuu password 'ppp';
> alter user uuu set default_transaction_read_only = on;
> GRANT select on all tables in schema zzz to uuu;
>
> So I only added the grant usage and it worked fine.
> What do people use day to day?
>
> I had frankly never explored the default_transaction_read_only
> parameter ...
>
>
> --
>
> regards
> Matthias Ritzkowski
> -marlinmobile-
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best practice to create a read-only user?
@ 2013-05-08 03:28 Sergey Konoplev <gray.ru@gmail.com>
parent: matthias ritzkowski <matthias@marlinmobile.com>
1 sibling, 0 replies; 3+ messages in thread
From: Sergey Konoplev @ 2013-05-08 03:28 UTC (permalink / raw)
To: matthias ritzkowski <matthias@marlinmobile.com>; +Cc: pgsql-admin
On Fri, May 3, 2013 at 7:03 AM, matthias ritzkowski
<matthias@marlinmobile.com> wrote:
> What do people use day to day?
I usually set default privileges for user postgres like below and
create end users in particular roles, either role_ro for read only or
role_rw for read-write access. All the database objects one need the
default privileges to be applied to must be created with user
postgres.
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
GRANT SELECT ON SEQUENCES TO role_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
GRANT SELECT ON TABLES TO role_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
GRANT EXECUTE ON FUNCTIONS TO role_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
GRANT SELECT,USAGE ON SEQUENCES TO role_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO role_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
GRANT EXECUTE ON FUNCTIONS TO role_rw;
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-05-08 03:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-05-03 14:03 Best practice to create a read-only user? matthias ritzkowski <matthias@marlinmobile.com>
2013-05-03 15:05 ` E. S. <yields.falsehood@gmail.com>
2013-05-08 03:28 ` Sergey Konoplev <gray.ru@gmail.com>
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