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

Postgresql: manage users permissions with groups

I`m trying to manage users permissions using groups (roles), but it doesn’t work and I don’t know why.
What I want to do is something like this:

CREATE ROLE readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

CREATE USER my_user IN ROLE readonly_role;

With this I’d like to grant only select permissions to my_user, but when I check on the roles created, readonly_role does have only select permissions, but my_user has been created with all default permissions.

I’m using postgresql 15.

Tags:

2

Answers


  1. 0

    In PostgreSQL 15, use ALTER USER with SET ROLE to assign the user the role in order to control user permissions through groups (roles). Here is the code which I updated, in this code My_user inherits the readonly_role permissions and only has SELECT access to tables in the public schema;

    -- Create a readonly role and grant SELECT on all tables in the public schema
CREATE ROLE readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

-- Create a user and set their role to readonly_role
CREATE USER my_user;
ALTER USER my_user SET ROLE readonly_role;

    Hope it works 🙂

    Login or Signup to reply.
  2. 0

    Your commands will work and grant my_user the SELECT privilege on all tables. The user has the default permissions, but inherits the privileges from readonly_role.

    What you have forgotten, particularly in v15 where the privileges on schema public have changed, is to grant the USAGE privilege on that schema:

    GRANT USAGE ON SCHEMA public TO readonly_role;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search