Managed PostgreSQL users and permissions
Every Managed PostgreSQL service has a main user, upadmin. You can add more users in the Control Panel, with the API, or with SQL, and they all start with the same access. For step-by-step instructions, see How to restrict user access in Managed PostgreSQL.
The upadmin user
upadmin can create users and databases, grant access to other users, and end other users' connections. It isn't a superuser. Its username and password are in the service's connection details.
upadmin has the access of every user created in the Control Panel, with the API or by upadmin itself. It can work with those users' databases and tables, and grant access to them.
What new users can do
| Created in | Starting access |
|---|---|
| Control Panel or API | Can sign in and connect to databases that are open to every user. Can't read other users' tables, create tables in databases it doesn't own, or create databases or users. |
SQL (CREATE ROLE … LOGIN) | The same |
The Control Panel and the API have one access setting for PostgreSQL users: Grant replication privilege, or allow_replication in the API, which gives the user PostgreSQL's REPLICATION attribute. Give all other access with SQL GRANT statements.
Users you create with SQL appear on the service's Users tab and in the API's user list without a password. You can set one with Credentials in the Control Panel or with the API, and the user then signs in with it. Changing a user's password doesn't change its access.
Who can connect to a database
Every user can connect to defaultdb and create temporary tables in it, and the same applies to every new database, until you revoke that access from PUBLIC. Connecting doesn't let a user read or change other users' tables.
upadmin owns defaultdb and every database created in the Control Panel or with the API. A database created with SQL belongs to the user named in CREATE DATABASE … OWNER. In new databases, only the database owner can create tables in the public schema by default.
Connecting as another user
The connection details in the Control Panel and the API are for upadmin and use defaultdb as the database. To connect as another user, change the username, the password and the database name. A user that can't connect to defaultdb gets FATAL: permission denied for database "defaultdb" if you leave it unchanged.
A connection pool with a user set accepts only that user. For details, see Managed PostgreSQL connection pooling.
When changes take effect
- Access to tables that you grant or revoke applies to open connections from their next statement.
- A revoked
CONNECTstops only new connections. To close open ones, use Terminate connection in the service's Connections tab. - Changing a user's password closes its open connections, and new connections need the new password.
- Deleting a user in the Control Panel or with the API closes its open connections. The databases, tables and views it owned pass to
upadmin, and its views keep working for the users that have access to them. The access the user was given goes with it. DROP ROLEfails while the user owns databases, tables or views, or has been given access to any, withERROR: role "<user>" cannot be dropped because some objects depend on it. Delete users in the Control Panel or with the API instead.
PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada, and used with their permission.