Add read only user in Postgresql

How to access to Postgresql where only reading of data is required?

Lets create a new user called "reader" and password as "password" who will be given access to the database mydatabase with READ only access. The following sign denotes the sql prompt, which you do not need to type.

=#

The steps listed here are rather generic on PostgreSQL 11 onwards. There are other considerations for security and more complex database designs that you should look into in the future.

Step 1

Connect to the database as administrator.

psql -U postgres -H localhost

=# \c mydatabase

Step 2

Create the new user

=# CREATE USER reader WITH encrypted PASSWORD 'password';

=# GRANT CONNECT ON DATABASE mydatabase TO reader;

=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;

Determine the user exist with the following command

=# \du

Step 3

Verify this by login as that user. Additionally, the user access can be shown through the commands.

=# \l 

=# \l mydatabase

Connect to the selected database and list that user's privileges.

=# \c mydatabase

=# SELECT table_catalog, table_name from information_schema.role_table_grants WHERE grantee = "reader";

=# SELECT table_catalog, table_schema, table_name from information_schema.table_privileges WHERE grantee = "reader";

Managing user privileges

In the case where that user is no longer required, it is only prudent to remove that user from access to the database. It is common to maintain that user within the system, in case they need to assign the user to another database access.

=# REVOKE ALL ON DATABASE mydatabase FROM reader;

Adding user to an existing READ only role would allow faster management of users.

=# CREATE ROLE reader

=# CREATE USER dave IN ROLE reader

Exercise:

Document your steps for this intermediate exercise.

Create a new user with login access name "dave" and password "exercise". 

Create a database name "bookstore" with a table called "users". You are free to assign the field type to a sensible type. 

users(
id, 
name, 
email, 
options, 
created_at, 
updated_at)

Fill this table "users" with 2 rows of data.

Assign user "dave" to be able to connect to this database and read contents of any table in this database.

Connect as user "dave" and verify if the user can only read data, without being able to update its data.