postgresqlsecurityprivilegesaccess-control

Creating a Limited Privilege PostgreSQL Role for Backend Server


I am trying to create a role in postgres for the backend server and for security reasons I want to limit the backend_user privileges. The user have access only to database_x and the public schema and can do the following in all tables in database_x:

the user cannot do the following

I have followed this but all attempts have been unsuccessful.

EDIT:

CREATE user userx WITH ENCRYPTED PASSWORD 'mypass';
GRANT ALL PRIVILEGES ON DATABASE mydb TO userx;
GRANT ALL PRIVILEGES ON SCHEMA public TO userx;
grant all PRIVILEGES on all tables in schema public to userx;

REVOKE drop, alter ON ALL TABLES IN SCHEMA public FROM userx;
REVOKE alter, create, drop ON DATABASE mydb FROM userx;

Solution

  • Thanks to @Adrian Klaver the solution that worked:

    CREATE user userx WITH ENCRYPTED password 'password';
    
    GRANT CONNECT ON DATABASE mydb TO userx;
    GRANT USAGE ON SCHEMA public TO userx;
    grant SELECT, INSERT, UPDATE, DELETE on all tables in schema public to userx;
    

    The user cannot create, modify, delete a database, a table or a column in a table.