I have tried to write a script that should show me who has privileges on a database and what are these privileges. My problem is that the script is outputting that everyone has all the privileges connect, create, temp on all the databases, however that is not true.
My script is like this:
DO
$$
DECLARE
db_r record;
BEGIN
FOR db_r IN
select db.datname, rl.rolname, case
when has_database_privilege(rl.rolname, db.datname, 'connect, create, temp') then
cast ('connect, create, temp' AS pg_catalog.text)
when has_database_privilege(rl.rolname, db.datname, 'connect, create') then
cast ('connect, create' AS pg_catalog.text)
when has_database_privilege(rl.rolname, db.datname, 'connect, temp') then
cast ('connect, temp' AS pg_catalog.text)
when has_database_privilege(rl.rolname, db.datname, 'create, temp') then
cast ('create, temp' AS pg_catalog.text)
when has_database_privilege(rl.rolname, db.datname, 'connect') then
cast ('connect' AS pg_catalog.text)
when has_database_privilege(rl.rolname, db.datname, 'create') then
cast ('create' AS pg_catalog.text)
when has_database_privilege(rl.rolname, db.datname, 'temp') then
cast ('temp' AS pg_catalog.text)
end privilege
from pg_roles rl
cross join pg_database db
where rl.rolcanlogin
and db.datallowconn
and db.datname not in ('postgres', 'template0', 'template1')
order by db.datname, rl.rolname
LOOP
IF db_r.privilege is not null then
RAISE NOTICE '% has % privilege on database %;',
db_r.rolname, db_r.privilege, db_r.datname;
END IF;
END LOOP;
END
$$;
There must surely be something wrong in the logics of my script, but I cannot see it. Could anyone help me with it?
I ended up finding an answer by myself.
The problem was in the way I built my case statement.
I have corrected it and it is like this now:
DO
$$
DECLARE
db_r record;
BEGIN
FOR db_r IN
select db.datname, rl.rolname, case
when has_database_privilege(rl.rolname, db.datname, 'connect')
and has_database_privilege(rl.rolname, db.datname, 'create')
and has_database_privilege(rl.rolname, db.datname, 'temp') then
cast ('connect, create, temp' AS pg_catalog.text)
when has_database_privilege(rl.rolname, db.datname, 'connect')
and has_database_privilege(rl.rolname, db.datname, 'create') then
cast ('connect, create' AS pg_catalog.text)
when has_database_privilege(rl.rolname, db.datname, 'connect')
and has_database_privilege(rl.rolname, db.datname, 'temp') then
cast ('connect, temp' AS pg_catalog.text)
when has_database_privilege(rl.rolname, db.datname, 'create')
and has_database_privilege(rl.rolname, db.datname, 'temp') then
cast ('create, temp' AS pg_catalog.text)
when has_database_privilege(rl.rolname, db.datname, 'connect') then
cast ('connect' AS pg_catalog.text)
when has_database_privilege(rl.rolname, db.datname, 'create') then
cast ('create' AS pg_catalog.text)
when has_database_privilege(rl.rolname, db.datname, 'temp') then
cast ('temp' AS pg_catalog.text)
end privilege
from pg_roles rl
cross join pg_database db
where rl.rolcanlogin
and db.datallowconn
and db.datname not in ('postgres', 'template0', 'template1')
order by db.datname, rl.rolname
LOOP
IF db_r.privilege is not null then
RAISE NOTICE '% has % privilege on database %;',
db_r.rolname, db_r.privilege, db_r.datname;
END IF;
END LOOP;
END
$$;
The problem was that I was trying to test if a user/role has more than one privilege on a database using one function call. To correct it, I had to call the function once for every privilege using the operator and in my case statement, in order to evaluate the case that a user/role has more than one privilege on a database.
Now the code shows the correct output: what privileges a user/role has on a database.