Skip to main content
To create a user in PostgreSQL and give them read-only permissions to a specific database, you can follow these steps.
This process involves using SQL commands within your PostgreSQL environment
1

Log into your PostgreSQL server

You can do this by using a terminal or an SQL client, connecting with a user that has sufficient privileges to create other users and manage permissions, such as the postgres superuser.
sql
2

Create the user

Use the CREATE ROLE or CREATE USER command to create a new user. CREATE USER is effectively the same as CREATE ROLE but with the LOGIN ability enabled by default.
sql
Replace readonly_user with your desired username and secure_password with a strong password.
3

Grant connect permissions to the database

You need to grant the new user permission to connect to the specific database.
sql
Replace target_database with the name of the database to which you want to grant access.
4

Grant usage permissions on schemas

Typically, you’d want the user to read data from specific schemas (commonly the public schema).
sql
5

Grant select permissions on tables and views

Finally, grant the SELECT permission on all tables and views within the schema. To automatically apply this to new tables as well, you can set up default privileges.
sql
This gives the readonly_user the ability to read data from all existing and future tables in the public schema of target_database.
6

Exit PostgreSQL session

After you’re done, you can exit the PostgreSQL command line by typing.
sql