Thread: Collumn level permissions ?

I do know a way to solve this. I could create a view "B" that is a view of
tab;e "A" without column "C" on the select, and give a user permissions on
B, but not on A, or at least I think that would work.

What I specifically need to do is "hide" one column of a table from a set
of users.

Any better way to do this?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On 8/19/19 12:17 PM, stan wrote:
> I do know a way to solve this. I could create a view "B" that is a view of
> tab;e "A" without column "C" on the select, and give a user permissions on
> B, but not on A, or at least I think that would work.
> 
> What I specifically need to do is "hide" one column of a table from a set
> of users.
> 
> Any better way to do this?

https://www.postgresql.org/docs/11/sql-grant.html

> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

stan wrote:
> I do know a way to solve this. I could create a view "B" that is a
> view of
> tab;e "A" without column "C" on the select, and give a user
> permissions on
> B, but not on A, or at least I think that would work.
> 
> What I specifically need to do is "hide" one column of a table from a
> set
> of users.
> 
> Any better way to do this?

You can use column permissions:

REVOKE SELECT ON mytable FROM auser;
GRANT SELECT (col1, col2) ON mytable TO auser;

This will cause any query that tries to read any other
column to fail with an error.

If you don't like that, your technique is good, but don't
forget to add "WITH (security_barrier = true)" so that nobody
can subvert security.

Yours,
Laurenz Albe
-- 
+43-670-6056265
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com