From: Stephen Frost <sfrost@snowman.net>
To: Shreeyansh Dba <shreeyansh2014@gmail.com>
Cc: KhunSanAung.GIS@gmail.com, pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: How to create a read only user account to access to all pg database?
Date: Wed, 25 Nov 2015 08:23:32 -0500
Message-ID: <20151125132332.GU3685@tamriel.snowman.net> (raw)
In-Reply-To: <CAGDYbUN0iv97o0p=hZqeVmqh33YVmrGDhv3XcHs6bpy_VjuqJw@mail.gmail.com>
References: <CAAQr1jZihBKxfXk8q=Y60RVqCixdJ2yrp_HLNuja0NKDM_PGLQ@mail.gmail.com>
<CAGDYbUN0iv97o0p=hZqeVmqh33YVmrGDhv3XcHs6bpy_VjuqJw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: sfrost@snowman.net, shreeyansh2014@gmail.com
Subject: Re: How to create a read only user account to access to all pg database?
In-Reply-To: <20151125132332.GU3685@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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