Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l2sNi-0006zz-Me for pgsql-admin@arkaria.postgresql.org; Fri, 22 Jan 2021 09:05:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l2sNg-00053V-1c for pgsql-admin@arkaria.postgresql.org; Fri, 22 Jan 2021 09:05:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l2sNf-00053N-9j for pgsql-admin@lists.postgresql.org; Fri, 22 Jan 2021 09:05:23 +0000 Received: from sonic308-54.consmr.mail.gq1.yahoo.com ([98.137.68.30]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1l2sNb-0005r1-DI for pgsql-admin@lists.postgresql.org; Fri, 22 Jan 2021 09:05:21 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1611306315; bh=0RFjGGfpm1DvFnss+W/NNi7ncQ5tLLaf3NG6/qNQaaA=; h=Date:From:To:In-Reply-To:References:Subject:From:Subject:Reply-To; b=cESYseK0yq7foMj/dlNS6Y0BD/nuj9TXOXVkIGKmUzmfN7tkTINPyNofB9c72iFiM0+1glzB3MecG2U/c1ZAnANP0G9irX16DKJdNCmcEPSgkRILKJSJgapTucvmw2C5IsAikR1Ffhz9KnD/ZqLYdYf2TEZ+R3F7Y88xp5M8jrxu5oeBl1+H5QXqrVAuJcvdUBf0MbtD9a8iI7psnaQtDo9N6X0FQtAwrU38wXxjJSjjfQ8dr+neFzriucETGDzxI3AL4mZ+lQ7zETh++EGUmSEHk6moaz/Jm0BVN+PDiE3H82GkCxz2rCsGPfLSZemZhgnMLsFyaW7TGFbqZHfYrA== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1611306315; bh=/SEb7xfA6psSUxMVEbl1iuy+shIsgvrBJoyDi6w31Od=; h=Date:From:To:Subject:From:Subject:Reply-To; b=GPAk1T1sDBKd+pItqwxyopID9qKvF4Fn9M47voOIWay0xMZmW/IdF+t67VI5LV2pYWCR9xzQC1UMLMRFALBCFNeES26XbW2FZfYmXoCAskDfqDAGm5RuWd6eiQOBBgFKqhr6v3CDk1SC5jjnIpfSGaWv37Jf1Z/HK8EflSuzBb1YA/FTl5lik4ea0vOpG4T9kGXbfzRI+KRwW2v4Tca5gIp2qFfLYSmD45zeki13eYu5z+oZxw2oKqxLV+hF6mQTMy/STwHwEWPJliRJbm0u3mgTcj2Fi9sYI6LlQn9148gJd3H+apDbfXmXKcEIGl89tVANGtKu3tDXZDZ9kNVcSw== X-YMail-OSG: nhX56pUVM1lrNthn5cu08K4nEfrivaxJEvOW2VXbSJvbdaLIYODLoFzWGhQ6Wdh sLemBJM.oHOfBLIBXN8rCYuVWZ6_5ITGnORjxbEGZOhFLjlh014LYEC3ma03aV0t7LW9YioUCMy6 RnHb7t3HY1TGEcHcSvs4Qz0pRdK8PRfCc_Muja6QgFTDiBM871khHWOEr1CJVfD5awC77vMAh8kl ghcZgdNlNcGbqLMAj0daU_cy3n1mYMaoy01qJq5rmoc_gFvYswHBCnMM00N3W2bqcRtNusEUeBMi Uk10hRGwfQjLDk5lgehXzgT_Jxfm5_hfaCY1RfD6azGZtKHzxGnvQrp1xG5c2BoHY68o3mDUrN9j jHay5l1LPxMBlHdYRLPRNDMw4HzwO81IWSA6MiF.b145B1ymmA7FI_wUEpvGruN_32yE6ZQrLisg vQqIWyQUWvOS_w16KMgrmIid8NOkbtVmbSOb0iUDs_4DKWQmKn5TkYbKzuFFMaozCPkNbhRARpog K2GhxY1e7GUYD9KslldH7dknoQIncZrVpBCXrc17SUJ_DKKp6gLqK18C7.At3nPHVvAkmiI7XSWU p5kpCLXgPcufFb7x9r7jCRF_Xj7hPgIgib7fe7u.7QAnwZF4aio_1SP89kr504lB30pclQW5giN2 erFjz16z7knGJQyM4v_A7r67REbfS6Q_H5pZUybwQ8NknnIikD9iAGfO_A3PYNASpFynhv1yi0sS rIgXVvroikHBKobW3I.dkZin8laygQfm8aepd.kJ8BTe2Yn.T5UP.vBmMi21pO7gz4F6qnnGqfZO d_s40_J8VqU2gTqiHqzAJMVnYOmL27hvDcq_y2D9727jHlpAKJYKzW4uLLsCu1Z0JitCSQgq1_o1 WlvM3RrJjNO9k14OS6SHLZpwmb.VRVsyWiktqqWjxzqDPt5Zo67hV4nHjcZtvxrCntWre7gpGiay msCxpnLHu9QCoIdQLxKdZ_XVlrZ0Yr2hkgvCBokfUZMfaRuFgcx.bXwg.ZiQjYKVXCR5yhBvWgqh ETJmC4kTyAcTtOIGihpXNWBYgaiRrGldAcVe9rqUMQ3Ul.LUd0B4sVIhXnjMHtRec0JBCSBsJFR_ Dw_TqXbWEGu2TpWlUwsch0r_luuDK3w3CjYpaCcgbaRBVQHHeYTxbDuSxkOhbr.9Ye7wTk67VfvO 2Txddm9rtyeDaQBtuSTcwVrPAdDeZLCIdp6M4rMuWnLn1hmKAe_HKhuYXVIVlFP3xvOLRSfXI1EX cGgPBU3i.V4WR7mCxfbM3y7WzQhdWZq3VhACDKbm1Yq38K1i8YglSDLrjOtb.xpB1ph6BbROLrSY 8rEJNXhzAA.JGGIxuTI8t.UvoF0Q5e.3IrOLVe.rRB6fJeXEg0UGLdgNlaEkSdTGXQFx4ol1ddVC W1chWGnC21y6NMtr6x2SKYitL435EkytGhkUqDcZ2nRhXzie8y6MSJndn8lqdPfbwQSN9AQ2Hu_H gtEb_BJwr_92A.k.F5wBkpmUS8SOQhcCu93YrsvujawYd60ptN_LXWCzeI1EIxd38KSa.wgxgcKR 2s36b2kBi21PDKuIHII2fQdiAM42wD5FRfLcdTlIlInLfluSfeCnuZ68IzwtdszbvLqjRXMAR3KP QhmLHFZ6io2PITrc25hiugd4kiHqkYmmiITOYSv8y8x1X.6VFqbXMaykmywr5BKqBP9RpmxEWAm3 HlLgzKzmRWAETML6bjC5FvTOf.EK0jH7YX8mkRPRsFAKcTNkiiy4cMHYFisYEOPyWbJYy1FfqUWg o5L6jfpNrQavGgz3rbBoQl6QYGCkDPQCxvLuzY4fsGu9HM_3y1mi49DzFD5BCp_Mkzg.WY8Rf0wU .N1YPt_fdmmo5Vr290csR1SMxXn89F8GqgoY5RNOE.xi4re8zWN0aKMlK_R4Xt4FelGH6QAjQQQG a4FZNS.o4SBiF7PIJi35EtDbSL.FNtZPbGhXvln.1V.Ei7Nf9By7xj7elSodQ50sch1bHG1iw9Xb rHyNk3ucZdpZ4CEOzN_iaja8jmDWI1yLP1m8rH6T6f3VLQ5zSMPxuN4E4IuT96Hoy38EvuTJZUJk 8eFel4Atr_QOoIgUtjfzlmUGzBVYLsWDBmwOH9yFOage8aplrD6ii_0l2DBn3tQp6.Tfw4k7OfWE LSfIENHqEaPCbxCLejElRumkENQ22FfB4aF5zG8St253JpI3AM7k03.n.S9VVJXreIqwCJYdbT7J BGq.KUs0e_tr.JJ3QBrwrIZ.5iM2reKKi2HxuRc3X9UmyHX5d_O6roFip_aGEzTsPum22CF9MhDk MR2kdu6H9GByBa6PaB6ahowQXT.uQcb717H3OTKZjZfFT.CQTecQ84oz.LDDkHp2yxXSrOwPi44Y Iifr7ZU9gGSU2Vou30yUilGSu_ZjxQoGCh48ysqw44zY0TUe9HeS9WhHe7uG.2y9Y7RcGzBt_Qez W2Ol5C8.KyFo7XIxjygki Received: from sonic.gate.mail.ne1.yahoo.com by sonic308.consmr.mail.gq1.yahoo.com with HTTP; Fri, 22 Jan 2021 09:05:15 +0000 Date: Fri, 22 Jan 2021 09:05:13 +0000 (UTC) From: Rohit Rajput To: Pgsql-admin , Yambu Message-ID: <1549478926.1190866.1611306313653@mail.yahoo.com> In-Reply-To: References: Subject: Re: Create Read only user MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1190865_136722122.1611306313651" X-Mailer: WebService/1.1.17501 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 11_1_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36 Content-Length: 2962 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1190865_136722122.1611306313651 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable By default, every user has permission to do anything on public schema. Bec= ause users are created outside of databases here. If you want to users not = to have any permission when those are created, use this: REVOKE ALL ON SCHEMA public FROM PUBLIC; and now provide permissions what you want to specific users. GRANT ALL/SELECT/.... ON SCHEMA public to your_user; Cheers On Friday, 22 January, 2021, 02:29:31 pm IST, Yambu wrote: =20 =20 Hello I created a user like this=C2=A0 CREATE USER user1 WITH PASSWORD ''; GRANT CONNECT ON DATABASE db1 TO user1; GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1; What amazes me is that when I connect using user1, I'm able to create=C2=A0= a table and drop it. How can this be? =20 ------=_Part_1190865_136722122.1611306313651 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
By default, every user has permission to do anything on public schema. Because users are created outside of databases here. If you want to users not to have any permission when those are created, use this:

REVOKE ALL ON SCHEMA public FROM PUBLIC;

and now provide permissions what you want to specific users.

GRANT ALL/SELECT/.... ON SCHEMA public to your_user;


Cheers
On Friday, 22 January, 2021, 02:29:31 pm IST, Yambu <hyambu@gmail.com> wrote:


Hello

I created a user like this 

CREATE USER user1 WITH PASSWORD '<password>';

GRANT CONNECT ON DATABASE db1 TO user1;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1;

What amazes me is that when I connect using user1, I'm able to create a table and drop it. How can this be?



------=_Part_1190865_136722122.1611306313651--