Sometimes as DB A to administrate PostgreSQL database, you need to create user accounts for certain group such as DEV  team(s) and grant every one in team with limited access privileges such as read only.

 

How can you create these accounts in batch then send user account with password to each user and ask them to reset password immediately?

 

Use following script as reference to create user account with password :

note:

valid until (2025-02-12T00:00:00-08:00)

^^^ create an account to valid for 1 week (from Feb 5 to Feb 12).

DO $$
DECLARE
user_roles TEXT[] := ARRAY[‘user1’, ‘user2’, ‘user3′,…,’userN’];
random_password TEXT;
username TEXT;
BEGIN
— Loop through each username
FOREACH username IN ARRAY user_roles
LOOP
— Generate a random password
SELECT substring(md5(random()::text), 0, 10) INTO random_password;

— Create the role with the generated password
EXECUTE format(‘CREATE ROLE %I LOGIN PASSWORD %L valid until (2025-02-12T00:00:00-08:00)’, username, random_password);

RAISE NOTICE ‘User “%” created ON DEV with password: %’, username, random_password;

— Grant readonly privilege to the user
EXECUTE format(‘GRANT readony TO %I’, username);

RAISE NOTICE ‘readonly privilege granted to user “%”‘, username;
END LOOP;
END $$;

 

Before doing above, check if you have readonly role exists and what privilege it has:

 

after you connect to pg:

 

run:

select * from database_privs(‘readonly’)

to check what privileges role readonly has. It shall have what its name implies but better double check.

 

then run:

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
where r.rolname not in (
'pg_signal_backend','rds_iam','rds_replication','rds_superuser','rdsadmin','rdsrepladmin'
)
and 'readonly' = any(select b.rolname from pg_catalog.pg_auth_members m 
join pg_catalog.pg_roles b on m.member = r.oid)
ORDER BY 1;

to check who is in role readonly.

if readonly role is existing, run following to create it:

create role readonly;
grant pg_read_all_data to readonly;

another script to check who is in role readonly:

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
where r.rolname in ('readonly')
ORDER BY 1;

script to create function  database_privs():

CREATE OR REPLACE FUNCTION database_privs(text) RETURNS table(username text,dbname name,privileges  text[])
AS
$$
SELECT $1, datname, array(select privs from unnest(ARRAY[
( CASE WHEN has_database_privilege($1,c.oid,'CONNECT') THEN 'CONNECT' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMPORARY') THEN 'TEMPORARY' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMP') THEN 'CONNECT' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL) FROM pg_database c WHERE 
has_database_privilege($1,c.oid,'CONNECT,CREATE,TEMPORARY,TEMP') AND datname not in ('template0');
$$ language sql;

CREATE OR REPLACE FUNCTION table_privs(text) RETURNS table(username text, relname regclass, privs text[])
AS
$$
SELECT  $1,c.oid::regclass, array(select privs from unnest(ARRAY [ 
( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys')  and c.relkind='r' and
has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;

scripts are got from https://www.enterprisedb.com/blog/list-user-privileges-postgresqlppas-11

and in following link there are many useful PG scripts:
https://www.postgresql.org/docs/11/functions-info.html