Re: PG V9.6 / REVOKE SELECT columns ON TABLE T1 FROM John

Hi,
you could have a look at this answer:
https://stackoverflow.com/questions/48317818/revoke-particular-columns-in-postgresql.
That is, first revoking SELECT privileges on the TABLE, and then GRANTING
the columns you want.
Anthony

PS: French can also be used on this French mailing list :)

On Fri, Oct 19, 2018 at 6:20 PM DECHERF Étienne <etienne(dot)decherf-ext(at)aphp(dot)fr>
wrote:

> Hello,
>
> I 've created a PostgreSQL user "John" with REVOKE SELECT columns from him.
> but "REVOKE SELECT (ipp , nom_nais , nom , prenom) ON TABLE table1 FROM
> "John";" command doesn't work in my script above :
>
> Here is how I created the user "John":
>
> CREATE ROLE "john";
> REVOKE ALL PRIVILEGES ON DATABASE pgdb FROM "john";
> ALTER ROLE "john" WITH LOGIN;
> ALTER ROLE "john" WITH PASSWORD 'password' VALID UNTIL '2018-12-31';
> GRANT CONNECT ON DATABASE dbpg TO "john";
> GRANT USAGE ON SCHEMA schema1 TO "john";
> ----- forbid access to 4 columns of Table1
> REVOKE SELECT (ipp , nom_nais , nom , prenom) ON TABLE Table1 FROM "John";
>
> Result :
> The 4 columns are still displayed.
> The "REVOKE SELECT column" doen't work. I manage always to select values
> in the 4 columns.
>
> Can you see why John can still watch the 4 columns ?
> Is a comand missing in this script ?
> Database version is 9.6.
>
> Thanks a lot for your help !
>
> Etienne DECHERF
> DBA
> etienne(dot)decherf-ext(at)aphp(dot)fr
> +33 6 67462246
>
>
>