How to Create a Read Only User in PostgreSQL

1. Grant Select/Usage to a Single Table
2. Multiple Tables/Views for PostgreSQL Version 9.0+
3. Create Read-Only User
4. Connect Postgres to Redash

Read-only user databases are beneficial for reporting because they have limited read-only access to the database and its table and fields.

A Postgres ready-only user means that the user can only read information from the database and is not allowed to write to it. So first, let’s learn how to create a Postgres read-only user.

Grant Select/Usage to a Single Table

If you’ve had a history with a user in Postgres, you have probably seen the GRANT and REVOKE commands. These have proved to be the fundamental basis of access control in Postgres.

While they are confusing to deal with, Postgres offers various access control settings for you to be able to navigate through them carefully. Another thing to note about these commands is that they don’t apply to new objects like tables introduced within a database.

There are default privileges in question when it comes to it, and these privileges are applied to a role by Postgres when a new object is created. However, you can modify these default privileges using the ALTER DEFAULT PRIVILEGES command.

Hence, if you only grant CONNECT to a database, the user is allowed to connect to the database but has no other privileges. Here, you have to go the extra mile and grant USAGE on namespaces/schemas and individually SELECT on tables and views.

We can do that as follows:

GRANT CONNECT ON DATABASE databasename TO user; -- databasename = your database name
GRANT USAGE ON SCHEMA public TO user;
GRANT SELECT ON tablename TO user;

Multiple Tables/Views for PostgreSQL Version 9.0+

Suppose you have the latest versions of PostgreSQL downloaded. In that case, it is easily possible for you to grant permissions on objects in the schema like tables/views, etc., using just a single command instead of typing them one by one as follows:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;

This line works to affect only those tables that have already been created. PostgreSQL also gives you to power to assign default roles to new objects automatically in the future as follows:

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

It is essential to note that the user who issued this command in the first place will be the only person who can assign these default roles to the objects and tables.

However, we can set it up on any role where the issuing user is the member. At the same time, you do not take up default privileges of all the roles where you’re a member when creating the new object.

You can adopt an approach where the database has an owning role, so the schema changes are performed on that specific owning role, so you assign default privileges to that owning role.

Multiple Tables/Views for PostgreSQL Versions Before 9.0

Whereas the features aren’t readily available in the earlier versions of PostgreSQL, it is still possible to create Postgres read-only users.

In length multi-table changes, it is recommended to use an automatic process to generate the needed GRANT SELECT to each view/table to avoid errors.

It is done in the following manner:

SELECT 'GRANT SELECT ON ' || relname || 'TO user; '
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r' ,'v' , 'S');

This command line, when executed, should output the relevant GRANT commands to GRANT SELECT on all sequences, tables, and views in public and for copy and paste. However, we must note that we will only apply it to those already created tables.

Create Read-Only User

Execute the following code to create a read-only user:

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'pass321.'
SUPERUSER INHERIT
NOCREATEDB NOCREATEROLE NOREPLICATION
VALID UNTIL
'infinity';

You can proceed to grant permission to the Postgres read-only user you just created:

GRANT CONNECTION ON DATABASE databasename TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;

However, these commands only work on the tables that have already been created in the database. To assign permissions to all the new tables created in the future:

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

It is important to note that instructions from GRANT USAGE onwards that grant permission on tables are to be run in the desired database. Otherwise, the permissions will not work.

To accomplish this, you can enter \connect databasename; to connect the database.

Connect Postgres to Redash

Without the GRANT/USAGE commands, permissions don’t work. Here, USAGE allows objects access contained in a specific schema as long as the object’s privilege requirements are met. First, create a read-only role:

CREATE ROLE myapp_readonly; 
GRANT CONNECT ON DATABASE database default TO myapp_readonly;
GRANT USAGE ON SCHEMA myapp TO myapp_readonly;

Proceed to then grant permission for the newly created role. Finally, please create a user with redash and assign the read-only role to it once you’re done connecting to the Postgres database with the new read-only user.

Creating a Postgres read-only user proves to be a great asset when handling the database. However, depending on your installed versions, the task’s difficulty can vary.

This article covers all bases for different versions of Postgres to ensure you’re well on creating a Postgres read-only user. Not only does that allow you to create a user with access rights to read the data, it keeps the user from performing any edits.