CODEWITHSUNDEEP
postgresqldatabase-administrationdatabase-permissions
shahzad ise
shahzad ise

Reputation: 45

Creating admin user in PostgreSQL

I am trying to create an admin role/user in PostgreSQL which should fulfil the following requirements:

  1. Should be able to do backup for the particular database (and not others)
  2. Should be able to create usernames which can access the particular database (and not others).
  3. Should be able to create/delete tables in the specific database and not other database
  4. Should not be able to create other data bases.

This is what I have so far:

create role dba with nosuperuser createdb createrole nologin replication bypassrls;
grant usage on schema public to dba;
alter default privileges in schema public grant all on tables to dba;
alter default privileges in schema public grant all on sequences to dba;
grant connect on database myDatabase to dba;
grant usage on schema public to dba; 
grant select on all tables in schema public to dba; 
grant select on all sequences in schema public to dba;
grant all privileges on all tables in schema public to dba;
create user dba_user login inherit encrypted password 'password' in role dba;

Please advise how to modify the above code to fulfill the requirements.

Upvotes: 0

Views: 5456

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

To achieve that, perform the following modifications:

  • Transfer ownership of the database and all schemas and objects in it to the new user.

  • Give the user CREATEROLE.

  • Make sure to REVOKE CONNECT ON all databases FROM PUBLIC. Grant the new user the CONNECT privilege on the database in question.

  • Don't give the new user any permissions on other databases or objects therein.

Upvotes: 1

Related Questions