From: Tom Lane <tgl@sss.pgh.pa.us>
To: ROS Didier <didier.ros@edf.fr>
cc: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>,
        "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: problem with read-only user
In-reply-to: <0d4a7143cb7b4a749ca7e4603e6a795e@PCYINTPEXMU001.NEOPROD.EDF.FR>
References: <0d4a7143cb7b4a749ca7e4603e6a795e@PCYINTPEXMU001.NEOPROD.EDF.FR>
Comments: In-reply-to ROS Didier <didier.ros@edf.fr>
	message dated "Fri, 20 Dec 2019 13:01:50 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2742.1576850697.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 20 Dec 2019 09:04:57 -0500
Message-ID: <2743.1576850697@sss.pgh.pa.us>
Precedence: bulk

ROS Didier <didier.ros@edf.fr> 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 k=
idsdpn03_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  tab=
les. =


You only showed us part of what you did ... but IIRC, =

ALTER DEFAULT PRIVILEGES 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