Why do we need a read-only user?
Creating a read-only user in any database is essential for several reasons, especially when it comes to security, data integrity, and access control.
In this guide, we'll explore how to create a read-only user in PostgreSQL. Read-only users are useful when you need to provide access to specific users without allowing them to make changes to the data,
Prerequisites
psql
CLI is installed on your machine.Access to the PostgreSQL admin account.
Log in to PostgreSQL
First, log in to your PostgreSQL instance using the psql
command-line interface. You can do this by running the following command on your terminal or command prompt:
psql -U postgres -h dbhosturl
Here, -U postgres
specifies the username of the admin, and -h
is the host where PostgreSQL is running.
Create a New User
Now, let's create a new user that will be granted read-only access. To create the user, execute the following command:
CREATE USER readonly_user WITH PASSWORD 'strongpassword';
Replace 'readonly_user'
with the desired username and strongpassword
with a strong password.
Grant Connection Privileges
Once the user is created, we need to grant the user the ability to connect to the desired database. Suppose the database name is mydatabase
, the command to grant connection privileges is as follows
GRANT CONNECT ON DATABASE mydatabase TO readonly_user;
This grants the user the right to connect to the specified database (mydatabase
).
if we have multiple databases, you can give connect to access to other databases as well.
GRANT CONNECT ON DATABASE anotherdb TO readonly_user;
Grant USAGE Privileges on Schema
PostgreSQL databases are organized into schemas, and before allowing the user to access the tables, we must grant them the ability to use the schema.
If you prefer to see the schemas you can run this
SELECT schema_name
FROM information_schema.schemata;
after you get the schemas name, you GRANT USAGE on schemes
GRANT USAGE ON SCHEMA schema_name TO username;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT USAGE ON SCHEMA userschema TO readonly_user;
Grant SELECT Privileges on Tables
granting the user the ability to read data from the tables. This can be done table by table,
Running below command will require an admin to connect to that DB.
For single table
GRANT SELECT ON table_name TO username;
For all tables
but if you want to allow access to all tables in the schema, you can use the following command
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
This command grants the SELECT
privilege, which allows the user to query all the tables in the public
schema without modifying them.
Apply Future SELECT Privileges (Optional)
If you expect new tables to be created in the schema in the future and you want the readonly_user
to automatically gain access to those new tables, you can modify the default privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;
This command ensures that any future tables created in the schema will have the SELECT
privilege automatically granted to the user.
Verify Permissions
Once you've granted the necessary privileges, you can verify the user's permissions by logging in as the read-only user
psql -U readonly_user -d mydatabase -h localhost
Try running a SELECT
query to confirm that the user has read-only access:
SELECT * FROM some_table;
If the user is correctly set up, the query should work, but any attempts to modify data (e.g., INSERT
, UPDATE
, or DELETE
) should fail with permission-denied errors.