RE: problem with read-only user

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
[email protected]
T=E9l. : +33 6 49 51 11 88

-----Message d'origine-----
De=A0: [email protected] [mailto:[email protected]] =

Envoy=E9=A0: vendredi 20 d=E9cembre 2019 15:05
=C0=A0: ROS Didier <[email protected]>
Cc=A0: [email protected]; [email protected]
Objet=A0: Re: problem with read-only user

ROS Didier <[email protected]> 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.