Postgres Role Required to Grant CONNECT on Specific Database

database-administrationpostgresqlpsqluser-management

I am trying to GRANT CONNECT on a DATABASE to a user as a schema owner in that database.

The schema owner is not a superuser.

Do I need to be the owner of a database to execute

GRANT CONNECT ON DATABASE DBXXX TO USER_YYY; ??

What role should a user be granted other than superuser to allow it to grant connect?

Best Answer

If you were given CONNECT WITH GRANT OPTION you can then grant connect to others.

If you were not given connect with grant option, you cannot grant it, you will need a superuser to grant you CONNECT GRANT OPTION, or to grant connect to the new user.

A superuser (or database owner) would use this command to grant grant option for connect:

GRANT CONNECT ON DATABASE database_name TO your_username WITH GRANT OPTION;

As a schema owner you can grant usage on your schema to others.

Related Solutions

PostgreSQL: Permission to execute function (that inserts into a table) but no permission to insert directly

You have to have another role, that has privileges to write directly to tables. Then you create the function using this another role, and add to function definition "SECURITY DEFINER" clause. And then you grant execute on this function to your web role.

You can read more about it in documentation.

You might also want to check blog post that I once wrote about securing database.

Ssh – can not connect via SSH to a remote Postgresql database

Check if the remote host allow tunneling. You can check with the client if the tunnel is refused with:

ssh -v -L 3333:myHost:5432 myUser@myHost

Best Answer

Related Solutions

PostgreSQL: Permission to execute function (that inserts into a table) but no permission to insert directly

Ssh – can not connect via SSH to a remote Postgresql database

Related Topic