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?