Creating a Read-only Database User in PostgreSQL

Creating a read-only user for a PostgreSQL database involves a few steps:

1. First, you'll want to log in to the PostgreSQL command line interface using the psql command.
2. Once you're inside, you'll create the user (often referred to as a "role" in PostgreSQL parlance). Let's name the user "readonly_user".
3. Then you'll grant that user the ability to connect to your database and grant the select permission on all tables in the database. You'll also want to ensure they can read sequences if you have any (e.g., for serial columns).

Here's a step-by-step guide using db as the example database:

• Log in to the PostgreSQL database.
• Create the user:

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'yourpassword';

• Grant connect privilege to the database:

GRANT CONNECT ON DATABASE db TO readonly_user;

• Grant SELECT on all tables in the database:

\c db
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

• Grant SELECT on all sequences:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;

• Set the privileges to be applied to future tables and sequences (If you want the readonly_user to have read access to tables and sequences that will be created in the future):

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON SEQUENCES TO readonly_user;

• Exit:

\q

Remember to replace 'yourpassword' with a strong password of your choice.