How to Modify User Privileges in PostgreSQL Databases

By default, PostgreSQL managed database clusters come with a user, doadmin, which has full access to every database you create. Instead of using doadmin to access the database, we recommend creating additional users who have only the privileges they need, following the principle of least privilege.

You can’t currently set a user’s privileges in the control panel, so you need to use a command-line PostgreSQL client like psql. After you create a user in the cluster, use psql to connect to the cluster as doadmin or another admin user. Connect to the database, then update the user’s permissions and verify that their access has changed.

Modify the User’s Permissions

First, connect to your database cluster as the admin user, doadmin, by passing the cluster’s connection string to psql.

psql "postgresql://doadmin:password@psql-do-user-123456-0.db.ondigitalocean.com:25060/defaultdb?sslmode=require"

This will bring you into the interactive shell for PostgreSQL, which changes your command prompt to defaultdb=>.

From here, connect to the database that you want to modify the user’s privileges on.

  
    
\connect example_database

  

You’ll see output like this, and your command prompt will change to the name of the database.

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "example_database" as user "doadmin".

From here, the commands you need to execute depend on the permissions you want the user to have.

As an example, to make a read-only user, first revoke all of the user’s default privileges, then give CONNECT access. From there, add SELECT privileges on the existing tables in the database and set SELECT privileges as their default for any other tables created in the future.

  
    
REVOKE ALL ON DATABASE example_database FROM example_user;
GRANT CONNECT ON DATABASE example_database TO example_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO example_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO example_user;

  

You need to run these commands on each database you want this user to have these privileges on.

You can also modify these commands to give the user different permissions. For example, changing the permissions from SELECT to INSERT, SELECT will make a user that can both read and write data. Learn more about PostgreSQL privileges in their documentation.

Check the User’s Privileges

Once you’re logged into the cluster, you can use the \du command to list users that currently exist and see their roles.

  
    
\du

  
                                       List of roles
 Role name     |                         Attributes                         | Member of
---------------+------------------------------------------------------------+-----------
 \_dodb        | Superuser, Replication                                     | {}
 doadmin       | Create role, Create DB, Replication, Bypass RLS            | {}
 postgres      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 example_user  |                                                            | {}

You can verify that the privilege change completed successfully by querying the database privileges table for the user.

  
    
SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'example_user';

  

You should see the new privileges in the output. For example, if you granted only SELECT privileges, the output would look like this:

 table_catalog    | table_schema | table_name | privilege_type
------------------+--------------+------------+----------------
 example_database | public       | account    | SELECT

You can also verify that the user’s permissions are changed by logging into the database cluster as the new user, then connecting to the database and testing commands. For example, if you try to INSERT into a database as a read-only user, you should receive an error like ERROR: permission denied for table account.