Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iklQj-0002t3-Fl for pgsql-hackers@arkaria.postgresql.org; Fri, 27 Dec 2019 08:57:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iklQg-0006My-5R for pgsql-hackers@arkaria.postgresql.org; Fri, 27 Dec 2019 08:57:06 +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_SHA1:256) (Exim 4.89) (envelope-from ) id 1iklQf-0006IL-Sg; Fri, 27 Dec 2019 08:57:05 +0000 Received: from mtagatef.edf.fr ([163.114.21.150]) by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1iklQY-00065g-9m; Fri, 27 Dec 2019 08:57:04 +0000 From: ROS Didier To: "tgl@sss.pgh.pa.us" CC: "pgsql-hackers@postgresql.org" , "pgsql-sql@postgresql.org" Subject: RE: problem with read-only user Thread-Topic: problem with read-only user Thread-Index: AdW3NS/eDLC6RUr/T3i9SHHRCOE3bwAAOQyAAVcmF9A= Date: Fri, 27 Dec 2019 08:56:55 +0000 Message-ID: <45e1df3aca6d4e7ab39606671b91be6f@PCYINTPEXMU001.NEOPROD.EDF.FR> References: <0d4a7143cb7b4a749ca7e4603e6a795e@PCYINTPEXMU001.NEOPROD.EDF.FR> <2743.1576850697@sss.pgh.pa.us> In-Reply-To: <2743.1576850697@sss.pgh.pa.us> Accept-Language: fr-FR, en-US Content-Language: fr-FR X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [10.22.153.70] x_exchange_neo: 1 Content-Type: text/plain; charset="iso-8859-1" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi Tom Thanks for your answer. Actually, you're right, the tables, the sequences are created by the user = kidsdpn03 and another read-only role (kidsdpn03_ro) must interrogate these = objects. So every time the kidsdpn03 role creates a new table, the kidsdpn03_ro rol= e will not have the rights to read them. Kidsdpn03_ro must be explicitly gr= anted read rights on this objects. Can you confirm that if it was the kidsdpn03_ro role that created the tabl= es, there would be no problem when accessing new tables? Thanks in advance. Didier ROS didier.ros@edf.fr T=E9l. : +33 6 49 51 11 88 -----Message d'origine----- De=A0: tgl@sss.pgh.pa.us [mailto:tgl@sss.pgh.pa.us] = Envoy=E9=A0: vendredi 20 d=E9cembre 2019 15:05 =C0=A0: ROS Didier Cc=A0: pgsql-hackers@postgresql.org; pgsql-sql@postgresql.org Objet=A0: Re: problem with read-only user ROS Didier writes: > I created a read-only role as follows: > psql -p 5434 kidsdpn03 > CREATE ROLE kidsdpn03_ro PASSWORD 'xxx'; ALTER ROLE kidsdpn03_ro WITH = > LOGIN; GRANT CONNECT ON DATABASE kidsdpn03 TO kidsdpn03_ro; GRANT = > USAGE ON SCHEMA kidsdpn03 TO kidsdpn03_ro; GRANT SELECT ON ALL TABLES = > IN SCHEMA kidsdpn03 TO kidsdpn03_ro; GRANT SELECT ON ALL SEQUENCES IN = > SCHEMA kidsdpn03 TO kidsdpn03_ro; ALTER DEFAULT PRIVILEGES IN SCHEMA = > kidsdpn03 GRANT SELECT ON TABLES TO kidsdpn03_ro; ALTER ROLE = > kidsdpn03_ro SET search_path TO kidsdpn03; > but when i create new tables, i don't have read access to those new tabl= es. = You only showed us part of what you did ... but IIRC, ALTER DEFAULT PRIVILE= GES only affects privileges for objects subsequently made by the same user = that issued the command. (Otherwise it'd be a security issue.) So maybe you didn't make the tables = as the same user? regards, tom lane Ce message et toutes les pi=E8ces jointes (ci-apr=E8s le 'Message') sont = =E9tablis =E0 l'intention exclusive des destinataires et les informations q= ui y figurent sont strictement confidentielles. Toute utilisation de ce Mes= sage non conforme =E0 sa destination, toute diffusion ou toute publication = totale ou partielle, est interdite sauf autorisation expresse. Si vous n'=EAtes pas le destinataire de ce Message, il vous est interdit de= le copier, de le faire suivre, de le divulguer ou d'en utiliser tout ou pa= rtie. Si vous avez re=E7u ce Message par erreur, merci de le supprimer de v= otre syst=E8me, ainsi que toutes ses copies, et de n'en garder aucune trace= sur quelque support que ce soit. Nous vous remercions =E9galement d'en ave= rtir imm=E9diatement l'exp=E9diteur par retour du message. Il est impossible de garantir que les communications par messagerie =E9lect= ronique arrivent en temps utile, sont s=E9curis=E9es ou d=E9nu=E9es de tout= e erreur ou virus. ____________________________________________________ This message and any attachments (the 'Message') are intended solely for th= e addressees. The information contained in this Message is confidential. An= y use of information contained in this Message not in accord with its purpo= se, any dissemination or disclosure, either whole or partial, is prohibited= except formal approval. If you are not the addressee, you may not copy, forward, disclose or use an= y part of it. If you have received this message in error, please delete it = and all copies from your system and notify the sender immediately by return= message. E-mail communication cannot be guaranteed to be timely secure, error or vir= us-free.