Postgres: Create a read-only user

This is a precise guide to create Postgres users, granting privileges, grant database read-only access along basic Postgres commands.

Prerequisite:

- Install Postgres on your machine, in this blog we are using postgres (PostgreSQL) 14.0.
- Create databases.
- Create tables.

Commands:

• Log in as a superuser:
  psql postgres

Postgres Login

• List of users/roles:
  \du

List of Roles

• Create Postgres user:
  CREATE ROLE <username> WITH LOGIN PASSWORD '<password>';
username: postgres_readonly
  password: password

Create User

• List of databases:
  \list
• Grant the connect access: For multiple databases, you have to provide access to all the databases individually.
  GRANT CONNECT ON DATABASE <database_name> TO <username>;

Grant Access

• Grant usage on schema:
  GRANT USAGE ON SCHEMA <schema_name> TO <username>;
  schema_name: public

Grant schema usage

• Use database:
  \c album_development;
• List of tables:
  \dt

List of tables

• Grant Select for tables:

- Grant Select for a specific table:
GRANT SELECT ON <table_name> TO <username>;

- Grant Select for multiple tables:
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <username>;

• Suppose we want to grant access to the new tables created in the future as well, then we will have to alter the default as below:
  ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name>
GRANT SELECT ON TABLES TO <username>;

Thanks for reading this. I hope this will be helpful for you. Please share your feedback in the comment if any.

Stay Tuned…