CODEWITHSUNDEEP
sqlpostgresqlprivileges
duck_goes_quack
duck_goes_quack

Reputation: 35

PostgreSQL Default Permissions not applying to other users

I am a beginner to the world of RDBMS and have started my dive at work with PostgreSQL using PgAdmin 4. I have been tasked to manage the database for my team and have started learning about permissions.

I have stumbled across the Default privileges property for schemas. I understood that the privileges defined here apply to any objects created within the schema. I have created a group role for the Admin (which I am currently the sole member of) of the team database in case I change role or whatever in the future. This way a new user can be added to this Admin group without having to manually add them to the permissions to each individual object if I should leave. All permissions (including GRANT) have been applied as default table permissions to this Admin group for all schemas.

However, I have found that when other users that have CREATE permissions add new tables to schemas, the default privileges are not being applied. Even as the owner of the schema I cannot see or access any of the properties of the tables my teammates are creating. I have had to ask the SuperUser account to manually add me to be able to fix the permissions on these tables.

My questions is: why are the default permissions I have defined not being applied to objects created to other users. Is there a way to ensure all users adhere to the same set of default permissions?

Here are the SQL statement I have been using:

GRANT ALL ON SCHEMA "schema_name" TO "user_1"

GRANT ALL ON SCHEMA "schema_name" TO "user_1"

ALTER DEFAULT PRIVILEGES IN SCHEMA "schema_name"
GRANT ALL ON TABLES TO "admin_me" WITH GRANT OPTION;

ALTER DEFAULT PRIVILEGES IN SCHEMA "schema_name"
GRANT SELECT ON TABLES TO "user_1";

ALTER DEFAULT PRIVILEGES IN SCHEMA "schema_name"
GRANT SELECT ON TABLES TO "user_2";

where admin_me is the group where I am in, and user_1 and user_2 are not part of the admin_me group.

Thanks!

Upvotes: 0

Views: 3084

Answers (2)

duck_goes_quack
duck_goes_quack

Reputation: 35

Thanks to Laurenz Albe for explaining Default Priviliges. I managed to find a solution to my problem using Event Triggers (also thanks to Laurenz Albe). I created an Event Trigger to react to CREATE TABLE tags and execute a Trigger Function to grant the "default" permissions.

CREATE OR REPLACE FUNCTION trg_create_table_func()
RETURNS event_trigger
LANGUAGE plpgsql
AS
$$
DECLARE obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag in ('CREATE TABLE')
        LOOP
            EXECUTE format('GRANT ALL ON TABLE %s TO "admin_me" WITH GRANT OPTION;', obj.object_identity);
            EXECUTE format('GRANT SELECT ON TABLE %s TO "team_group";', obj.object_identity);
        END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_create_table ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE PROCEDURE trg_create_table_func();

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 248215

There is a fundamental misunderstanding here: privileges on a schema do not determine or influence permissions or ownership of tables in that schema.

If user A is a member of the admin group, and that group has the CREATE privilege on the schema, user A can create a table in that schema. But that table will belong to A, not to admin.

Now only the owner of a table or the superuser can ALTER or DROP the table of GRANT permissions on it. That is why admin and other users in that group cannot change permissions on the table.

If all you need is to change permissions, you could run the following as user A:

GRANT ALL ON TABLE newtab TO admin WITH GRANT OPTION;

Then any member of admin can manage permissions on that table. There is no such option to delegate ALTER TABLE or DROP TABLE to others.

Upvotes: 1

Related Questions