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.
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