Check the perm 'Create Database' in PostgreSQL as well as in SQL Server

Question

I would like to check the perm in PostgreSQL db as well as I do it via this command 'SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'CREATE DATABASE')' for the SQL server.

What is the best way to do the same in PostgreSQL? I didn`t find the similar function in PostgreSQL.

score 2 · Accepted Answer · 2021-11-17 11:23:27Z

2

The privilege to create a new database is stored in pg_roles

To check if a specific user can create a new database:

select rolcreatedb
from pg_catalog.pg_roles
where rolname = current_user; --<< or replace with a specific user name

If you want to see all roles that are allowed to create a new database

select rolname
from pg_catalog.pg_roles
where rolcreatedb;

edited Nov 17, 2021 at 11:23

answered Nov 17, 2021 at 10:49

user330315

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

2 Comments

Mykyta Halchenko Over a year ago

thanks, I am trying to do it for the current user without filtering by rolname column as it is implemented in MS SQL HAS_PERMS_BY_NAME. Do you know how to do it better than below? I can do it something like that: select rolcreatedb from pg_catalog.pg_roles where rolname in (select current_user)

user330315 Over a year ago

Then use where rolname = current_user in the first query

Collectives™ on Stack Overflow

Check the perm 'Create Database' in PostgreSQL as well as in SQL Server

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related