Create DML, DDL, DQL user in PostgreSQL

Question

I need to create 3 users with different DML, DDL, DQL on newly created clean Database in PostgreSQL.

DML should have SELECT, UPDATE, DELETE
DDL should have CREATE, DROP, ALTER, TRUNCATE, INSERT
DQL should have SELECT

all of this in standard scheme public. Important is that user inherit right on newly created tables by DDL user.

users ref: https://www.geeksforgeeks.org/sql-ddl-dql-dml-dcl-tcl-commands/

I did some coding but I'm pretty new in PostgreSQL and it didn't work :(

The main problem was that I cannot perform GRANT or REVOKE on CREATE, DROP, ALTER, TRUNCATE :(

Can someone help please?

Maybe you have something similar already prepared?

user330315 · Accepted Answer · 2022-11-02 07:28:39Z

4

The setup you want, can be done to some extent. However these privileges are controlled on schema level, not on database level.

Assuming you have a schema app_schema for which this should be defined, you can do the following:

First create the users:

create user ddl with password '***';
create user dml with password '***';
create user dql with password '***';

Then create the schema:

create schema app_schema;

Then allow the ddl user to create objects:

grant create,usage on schema app_schema to ddl;

Then change the default privileges on the schema, so that every table (or view, or sequence ...) created by the ddl user is accessible by the dml and dql users:

alter default privileges 
   for role ddl
   grant select,update,delete on tables
   to dml;

alter default privileges 
   for role ddl
   grant select on tables
   to dql;

This will affect all future tables created in the schema by the user ddl.

The owner of the tables automatically has the privileges to INSERT,UPDATE,DELETE or TRUNCATE the tables.

I have never tried this, but it seems possible to revoke the UPDATE and SELECT privileges:

alter default privileges 
  for role ddl
  in schema app_schema
  revoke update,select,delete on tables
  from ddl;

If there are already tables in the schema, you need to grant the desired privileges for them:

grant select,insert,update,delete on all tables 
  in schema app_schema
  to dml;

grant select on all tables 
  in schema app_schema
  to dql;

answered Nov 2, 2022 at 7:28

user330315

Sign up to request clarification or add additional context in comments.

7 Comments

gabelvlad Over a year ago

Hi i executed below:

CREATE user dml with NOCREATEDB password 'testdml';  CREATE user ddl with NOCREATEDB password 'testddl'; CREATE user dql with NOCREATEDB password 'testdql';  create schema test_schema;   grant create,usage on schema test_schema to ddl;   alter default privileges     for role ddl    grant select,update,delete on tables    to dml;       alter default privileges     for role ddl    grant select on tables     to dql;       alter default privileges    for role ddl   in schema test_schema   revoke update,select,delete on tables   from ddl;

gabelvlad Over a year ago

Using ddl user i created test table in that scheme.

create table test_schema.candidatesddl (     candidate_id int generated always as identity,     first_name varchar(100) not null,     last_name varchar(100) not null,     email varchar(255) not null unique,     phone varchar(25) not null,     primary key(candidate_id) );

Inserted record:

INSERT INTO test_schema.candidatesddl(first_name, last_name, email, phone) VALUES('Joe','Com','[email protected]','408-111-2222');

gabelvlad Over a year ago

And i'm really sorry for formatting i'm new to stackoverflow and cannot get it done properly :(

gabelvlad Over a year ago

i checked CREATE, DROP , with user DML and i cannot perform that actions which are good and is planned. i checked also user DQL it doesn`t have any permissions also on scheme he cannot perform any actions, but also cannot perform SELECT

gabelvlad Over a year ago

Then relog into DML account and tried to perform SELECT: select * from test_schema.candidatesddl And got an error. Same for UPDATE, DELETE. I checked CREATE, DROP ** with user **DML and i cannot perform that actions which are good and as planned. I checked also user DQL it doesn`t have any permissions also on scheme, he cannot perform any actions, but also cannot perform SELECT Seems that inheritance of permissions on created tables by DDL is not working as expected. Am i doing smth wrong ?

|

Collectives™ on Stack Overflow

Create DML, DDL, DQL user in PostgreSQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related