Gant select on schema in Postgres-CodePudding

How do I grant select to all items within a schema in PostgreSQL?

I am trying to make a read-only user in my PostgreSQL database. All my tables and views are located within schema1. Sometimes, new views are added within schema1, but the read-only user would not have read access to the newly created views.

Is there a way to grant select on all items within a schema so the user has read access to any newly created schemas as well?

My current code is:

CREATE ROLE readonly;
GRANT CONNECT ON DATABASE postgres TO readonly;
GRANT USAGE ON SCHEMA schema1 TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA schema1 TO readonly;

I know I can add a line to grant select to views in a schema as well, but I am looking for a solution that will work when new views are also added.

CodePudding user response：

With event triggers you can handle that :

CREATE OR REPLACE FUNCTION auto_grant_func()
RETURNS event_trigger AS $$
BEGIN
    GRANT CONNECT ON DATABASE postgres TO readonly;
    GRANT USAGE ON SCHEMA schema1 TO readonly;
    GRANT SELECT ON ALL TABLES IN SCHEMA schema1 TO readonly;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER auto_grant_trigger
    ON ddl_command_end
    WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS')
EXECUTE PROCEDURE auto_grant_func();

CodePudding user response：

ALTER DEFAULT PRIVILEGES IN SCHEMA schema1 GRANT SELECT ON TABLES TO readonly;