To grant access to users for only viewing a database (i.e. no INESRTs or CREATE or UPDATE access) there are certain conditions.

First, access should be only allowed through ssh.

Second, the ssh user should not be able to get into a shell directly.

And finally, the database should restrict access to that particular user with relevant permissions.

All the commands below are run as root.
The user with READ_ONLY access is dbviewer
The database to which access is to be granted is testdb

Create linux user without shell but with home directory

List of user / group commands

$> useradd --shell /bin/false -d /home/dbviewer -m dbviewer

Setup ssh access

Add .ssh directory and authorized_keys file

$> mkdir /home/dbviewer/.ssh
$> touch /home/dbviewer/.ssh/authorized_keys
$> chown -R dbviewer:dbviewer /home/dbviewer/.ssh
$> chmod 600 /home/dbviewer/.ssh/authorized_keys

Add public ssh_key in authorized_keysfile

Create role in Postgres

From the postgres prompt –

postgres> createuser --interactive

This role should not be a superuser role. So in the prompts of the command above, enter ‘n’.

Example entry –

Enter name of role to add: dbviewer
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Set database password for the user

From the psql prompt –

psql> \password dbviewer

Grant relevant accesses

Reference: Stackoverflow

All commands below are from psql prompt

psql> \c testdb

Make sure you are connected to the relevant database before issuing the following commands!

Grant CONNECT access to database for user.

psql> GRANT CONNECT ON DATABASE testdb TO dbviewer;

Grant access to single table

psql> GRANT SELECT ON testtable TO dbviewer;

Grant access to all tables

psql> GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbviewer;

This only affects tables that have already been created. More powerfully, you can automatically have default roles assigned to new objects in future:

psql> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dbviewer;

IMPORTANT: Note that by default this will only affect objects (tables) created by the user that issued this command: although it can also be set on any role that the issuing user is a member of. However, you don’t pick up default privileges for all roles you’re a member of when creating new objects… so there’s still some faffing around. If you adopt the approach that a database has an owning role, and schema changes are performed as that owning role, then you should assign default privileges to that owning role. IMHO this is all a bit confusing and you may need to experiment to come up with a functional workflow.

Note: To prevent this user being able to create new tables. Otherwise, although they cannot modify data or structure of existing tables, they can still create new tables.

psql> REVOKE CREATE ON SCHEMA public FROM PUBLIC;