HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to grant Connect & Create Privilege on current Database in PostgreSQL?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlconnectprivilegecreategrantdatabasecurrenthow

Problem

After looking at the docs, I know that you can grant Connect & Create Privileges by using the following commands:

GRANT CREATE ON DATABASE  TO user;
GRANT CONNECT ON DATABASE  TO user;


How do I grant these privileges on the current database without specifying it's name?

I'm trying to write a script which will be used to grant all rights whenever a new database is created by my application, and hence I don't know what the name will be while writing the script.

Solution

You could use a DO block with dynamic SQL:

do 
$ 
begin
  execute format('grant create, connect on database %I to %I', current_database(), 'some_user');
end;
$;

Code Snippets

do 
$$ 
begin
  execute format('grant create, connect on database %I to %I', current_database(), 'some_user');
end;
$$;

Context

StackExchange Database Administrators Q#217340, answer score: 9

Revisions (0)

No revisions yet.