Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1a1a4V-0002sV-Cd for pgsql-admin@arkaria.postgresql.org; Wed, 25 Nov 2015 13:25:19 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1a1a4U-0001AC-VT for pgsql-admin@arkaria.postgresql.org; Wed, 25 Nov 2015 13:25:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1a1a2p-0006tV-NL for pgsql-admin@postgresql.org; Wed, 25 Nov 2015 13:23:35 +0000 Received: from tamriel.snowman.net ([2001:4830:167b::11]) by magus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1a1a2n-0000WW-2C for pgsql-admin@postgresql.org; Wed, 25 Nov 2015 13:23:35 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 5F3E85F799; Wed, 25 Nov 2015 08:23:32 -0500 (EST) Date: Wed, 25 Nov 2015 08:23:32 -0500 From: Stephen Frost To: Shreeyansh Dba Cc: KhunSanAung.GIS@gmail.com, pgsql-admin Subject: Re: How to create a read only user account to access to all pg database? Message-ID: <20151125132332.GU3685@tamriel.snowman.net> References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="BiU9qP9Ynht4bzVk" Content-Disposition: inline In-Reply-To: X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/3.13.0-61-generic (x86_64) X-Uptime: 08:18:36 up 112 days, 21:49, 11 users, load average: 0.17, 0.12, 0.08 User-Agent: Mutt/1.5.21 (2010-09-15) X-Pg-Spam-Score: -2.5 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --BiU9qP9Ynht4bzVk Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable * Shreeyansh Dba (shreeyansh2014@gmail.com) wrote: > A simple way is Create a user with superuser privileges and read only > permission. >=20 > -Login psql as postgres or other superuser. > -Create the new superuser role and set it to read only : >=20 > CREATE USER backupadmn WITH PASSWORD 'pwd' SUPERUSER ; > ALTER USER backupadmn set default_transaction_read_only =3D 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: ---------------------- =3D# begin read write; BEGIN =3D*# alter user r1 set default_transaction_read_only =3D 0; ALTER ROLE =3D*# commit; COMMIT =3D#=20 ---------------------- Thanks! Stephen --BiU9qP9Ynht4bzVk Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJWVbZTAAoJEO1sijiDR2RVt/YQAIDLoCdLg7wI7pSKIr2jG1Dn v7QQ4CozTgPwQZ+Zbax23jUzUsguEXbnRlRAf/Ou0YkHMUxAh3CerbpHcgBud4H7 fTUEZ/o0iuQLuSolllwiaoQsj7PL3grjk6s09EE0vewZ7BvtaWRf/Ur56HD85Xy7 DUNSV+bG2sgdHMChekaBNoabhO5M1FR2MYdXduITJ91B+OBzrCC8q6f8+rIeBCgV ofDpzPCSKTm/aVQnzXuiCy86ICc/nFb0TqqK7bsUsiY1LO3GKwBiJYWRSpwjTXYG OcOE66Zihw/BAvyr+Cvpbwj3lkASKHra0k21ndC8Ec4XB4HY0O07KpvXg0fnVK4n zyyNquUh7Lf4eipurYC0h5/0FwSjTUBuUNFhtvrZiE07Ac0Ry1jnQgl+U8O3M8/D 4zLZwffywlry5H3T8b9B2nsCQ/8krWZCvpFAAHPexjP+wUsZN+4q0JjDQtomDfAs Ny0Bu1XqPl4yQFnteNTWI65aVmrWGGaYKdDLxZYxJp3+c3/aL2uj12yLM0Ssxp7Z sL/5NMMeZr5qGHxbUqfGek6aO/71xkKp/LW2dOk63kGrEEvSnJsKSP7PInil4yjK iM9QT1V/4/mLXQapYdeF37sED0eON4LmAriy7OP2cd6OD1b4sGucPnsYHNfbW6xZ DtWVBHKVVjVrEwpPkIjC =YZnG -----END PGP SIGNATURE----- --BiU9qP9Ynht4bzVk--