How to Limit Access to Database in PostgreSQL

Sometimes you may need to restrict access to database in PostgreSQL, for security reasons or otherwise. In this article, we will look at how to limit access to database in PostgreSQL.

How to Limit Access to Database in PostgreSQL

Here are the steps to limit access to database in PostgreSQL. Basically, we will create a new role and grant it select permissions to our database tables. Then we will create a database user and assign it the new role that we have created. This way the database user will only have access to those tables which can be accessed by our newly created role.

1. Create Role

Log into PostgreSQL and run the following command to a new role. Replace role name as per your requirement.

postgres-# CREATE ROLE new_role;

Also read : How to Setup Remote Connection in PostgreSQL

2. Limit Permission

Run the following commands to restrict access of newly created role to database sample_database.

postgres-# GRANT CONNECT ON DATABASE sample_database TO new_role;

Further we will grant access to all tables in public schema. You can change schema name as per your requirement.

postgres-# GRANT USAGE ON SCHEMA public TO new_role;
postgres-# GRANT SELECT ON ALL TABLES IN SCHEMA public TO new_role;

If you want to grant select permission for specific tables (e.g sample_table) run the following command

postgres-# GRANT SELECT ON sample_table IN SCHEMA public TO new_role;

Similarly, if you have any view (e.g sample_view) for which you want to provide access to new_role, run the following command.

postgres-# GRANT SELECT ON sample_view IN SCHEMA public TO new_role;

Also read : How to List databases and tables in PostgreSQL

3. Create User

Finally, we create a PostgreSQL user and assign the newly created role to that user. Update new_user and user_password in commands below with your username and password.

postgres-# CREATE USER new_user WITH PASSWORD 'user_password';
postgres-# GRANT new_role TO new_user;

Also read : How to Create Read Only User in PostgreSQL

Hopefully, this article will help you limit user access in PostgreSQL. Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.