Postgres 14 - DDL and DML users

88 views Asked by At

I need to create 2 users for a database and schema, one for DDL queries and the other for DML queries. Is it possible and how can i do it? I'm running Postgres 14 on windows and pgAdmin to run queries

Kind regards

# connected as postgres 
create user tst_ddl with password 'Pwd@123';
create user tst_dml with password 'Pwd@123';

CREATE DATABASE testdb
    WITH
    OWNER = tst_ddl
    ENCODING = 'UTF8'
    LC_COLLATE = 'English_United States.1252'
    LC_CTYPE = 'English_United States.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

# connected as postgres @testdb
alter default privileges 
   for role tst_ddl
   grant select,update,delete on tables
   to tst_dml;

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

 # connected as tst_ddl

CREATE TABLE tst.tb_tsta
(
    col_a integer
);

ALTER TABLE IF EXISTS tst.tb_tsta
    OWNER to tst_ddl;

running the code above, i can create and alter the table structure but i can query the data also. What am i doing wrong?

0

There are 0 answers