To grant access to users for only viewing a database (i.e. no
UPDATE access) there are certain conditions.
First, access should be only allowed through
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
The user with READ_ONLY access is
The database to which access is to be granted is
Create linux user without shell but with home directory
$> 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
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
All commands below are from psql prompt
psql> \c testdb
Make sure you are connected to the relevant database before issuing the following commands!
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;