0

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
shahzad ise
  • 45
  • 2
  • 6

1 Answers1

1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • is there a way to transfer ownership from all tables in public schema using the SQL tool? using PgAdmin, it's very time consuming. – shahzad ise Dec 19 '18 at 15:48
  • `REASSIGN OWNED` would be nice, but cannot be restricted to a schema. A good solution might be `psql`'s `\gexec`. – Laurenz Albe Dec 19 '18 at 15:57