pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to create a read only user account to access to all pg database?
4+ messages / 4 participants
[nested] [flat]
* How to create a read only user account to access to all pg database?
@ 2015-11-25 03:35 KhunSanAung <khunsanaung.gis@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: KhunSanAung @ 2015-11-25 03:35 UTC (permalink / raw)
To: pgsql-admin
Hi All,
I have several pg database with their own login user account.
For backup purpose, I'd like to create an additional read only user account
that can access all the pg database.
How can I do it?
Via PSQL command or via pgAdmin III client would be okay.
Your help is high appreciated.
Many thanks & best regards
--
Have a nice day!
--
*Mr. Khun San Aung*
* <http://geoportal.icimod.org/>*
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to create a read only user account to access to all pg database?
@ 2015-11-25 06:14 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: KhunSanAung <khunsanaung.gis@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Shreeyansh Dba @ 2015-11-25 06:14 UTC (permalink / raw)
To: KhunSanAung.GIS@gmail.com; +Cc: pgsql-admin
Hi KhunSanAung,
A simple way is Create a user with superuser privileges and read only
permission.
-Login psql as postgres or other superuser.
-Create the new superuser role and set it to read only :
CREATE USER backupadmn WITH PASSWORD 'pwd' SUPERUSER ;
ALTER USER backupadmn set default_transaction_read_only = on;
Regards,
Sagar Jadhav
Database Administrator
www.shreeyansh.com
On Wed, Nov 25, 2015 at 9:05 AM, KhunSanAung <khunsanaung.gis@gmail.com>
wrote:
> Hi All,
>
> I have several pg database with their own login user account.
> For backup purpose, I'd like to create an additional read only user
> account that can access all the pg database.
> How can I do it?
> Via PSQL command or via pgAdmin III client would be okay.
>
> Your help is high appreciated.
>
> Many thanks & best regards
>
> --
> Have a nice day!
> --
>
> *Mr. Khun San Aung*
> * <http://geoportal.icimod.org/>*
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to create a read only user account to access to all pg database?
@ 2015-11-25 06:29 Ville Rantamaula <ville.rantamaula@gmail.com>
parent: KhunSanAung <khunsanaung.gis@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Ville Rantamaula @ 2015-11-25 06:29 UTC (permalink / raw)
To: KhunSanAung.GIS@gmail.com; +Cc: pgsql-admin
Hi,
Assuming you have only the public schema in use per database (usually the
case). In each database run the following SQL:
CREATE ROLE backup WITH LOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup;
After that, adjust the file PostgreSQL configuration file pg_hba.conf to
allow appropriate access for newly create ROLE backup.
Yours,
Ville
2015-11-25 5:35 GMT+02:00 KhunSanAung <khunsanaung.gis@gmail.com>:
> Hi All,
>
> I have several pg database with their own login user account.
> For backup purpose, I'd like to create an additional read only user
> account that can access all the pg database.
> How can I do it?
> Via PSQL command or via pgAdmin III client would be okay.
>
> Your help is high appreciated.
>
> Many thanks & best regards
>
> --
> Have a nice day!
> --
>
> *Mr. Khun San Aung*
> * <http://geoportal.icimod.org/>*
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to create a read only user account to access to all pg database?
@ 2015-11-25 13:23 Stephen Frost <sfrost@snowman.net>
parent: Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephen Frost @ 2015-11-25 13:23 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; +Cc: pgsql-admin
* Shreeyansh Dba (shreeyansh2014@gmail.com) wrote:
> A simple way is Create a user with superuser privileges and read only
> permission.
>
> -Login psql as postgres or other superuser.
> -Create the new superuser role and set it to read only :
>
> CREATE USER backupadmn WITH PASSWORD 'pwd' SUPERUSER ;
> ALTER USER backupadmn set default_transaction_read_only = on;
Uh, that doesn't create a read-only user, it just starts that user's
session out with the transaction being read only *by default*.
Do NOT use this to try and create read-only users.
The user can trivially change that if they have SQL access using begin:
----------------------
=# begin read write;
BEGIN
=*# alter user r1 set default_transaction_read_only = 0;
ALTER ROLE
=*# commit;
COMMIT
=#
----------------------
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-11-25 13:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-11-25 03:35 How to create a read only user account to access to all pg database? KhunSanAung <khunsanaung.gis@gmail.com>
2015-11-25 06:14 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2015-11-25 13:23 ` Stephen Frost <sfrost@snowman.net>
2015-11-25 06:29 ` Ville Rantamaula <ville.rantamaula@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