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;
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.