PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres hide some columns from user in SELECT *

I want to give a group of users selective access only to particular columns of an Application due a privacy concern. So, when user types ”’SELECT *”’ on restricted table, he could only see allowed columns.

What I want to achieve:

  1. Hide particular columns of a table from particular user group
  2. "SELECT *" works as usual, but hiding columns, which is not allowed
  3. DDL for the underlying table should not be forbidden, even if it could temporarily break the view (it’s okay to fix it later)

Let’s say:

  • the group name is "restricted_group"
  • the table is "users" (id, description, password)

The goal is:
When user from "restricted_group" does SELECT * FROM users, he must see only "id", "description" fields.

I tried two ways:

  1. Using views to underlying table:
CREATE VIEW restricted_view AS
SELECT "id", "description" FROM users;
GRANT SELECT ON restricted_view to restricted_group;

It does the job, but it PREVENTS any DDL on the table "users" due to Postgres’s restriction "cannot alter table because other objects depend on it".
Very strange behaviour in Postgres for me, because in Oracle it could just make view invalid, but not prevented DDL.
Using CASCADE or recreating VIEW is not feasible as a part of this task.

  1. Using explicit GRANT access to columns:
GRANT SELECT ("ID", "desc") ON users TO restricted_group;

It forbids users to make "SELECT * users" and requires to explicidly list all allowed columns, which isn’t user friendly. Because instead of getting data fast, user should check any column for access first.

Please, advise me a solution, which would met all initial requirenments?

Tags:

2

Answers


  1. 0

    There is none.

    You have insisted on the following requirements:

    1. If using a view it must be always available and valid.
    2. You intend to make changes to the underlying table which would invalidate the view.
    3. You are not prepared to recreate the view when the underlying table changes.
    4. You want queries to be able to specify "*" for all columns while not actually returning all columns.

    This set of requirements are simply incompatible.

    The obvious solution is to recreate the view when required, but you don’t say why that isn’t an option for you.

    Login or Signup to reply.
  2. 0

    Postgres doesn’t completely restrict modification of tables that feed views, just the ones that would break the view. You can by all means add/change/remove table columns as long as you’re not mutating the ones that something else depends on. It’s not just views, but also foreign key references, any pg_depend or pg_constraint entry.

    If you want a breakable view, or to simply decouple it from the underlying table, use a plain SQL table- or set-returning function, or put one between the view and the source table:

    create table test(id int,description text,priv text);
insert into test select 1,'description','priv';

select * from test;
    id description priv
    1 description priv

    As you observed, a regular, direct view will be tied to the table via dependency, blocking some alterations:

    create view v_test as select id,description from test; 
alter table test drop column description,
                 alter column id type bigint,
                 add column description uuid default gen_random_uuid();

    ERROR:  cannot drop column description of table test because other objects depend on it
DETAIL:  view v_test depends on column description of table test
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

    An intermediate function removes that dependency:

    create function f_test() returns table(id int, description text) 
security definer language sql as '
  select id,description from test';
drop view if exists v_test;
create view v_test as select * from f_test();
select * from v_test;
    id description
    1 description 
    alter table test drop column description,
                 alter column id type bigint,
                 add column description uuid default gen_random_uuid();

select * from v_test;
    id description
    1 94c7bb56-8ab1-4b2a-85a6-3ebb302acd50

    This will continue to work as long as the types found in the table can be coerced to what the function is supposed to return.

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search