Granting Read-Only Access in PostgreSQL: A Step-by-Step Guide
-
Create the User:
-
Use the
CREATE ROLE
command to create a new user, often called a role in PostgreSQL. For example:CREATE ROLE readonly_user WITH LOGIN PASSWORD 'your_password';
-
-
Grant Connection Permissions:
-
Grant Schema Permissions:
-
-
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
-
-
Optional: Set Default Privileges (For New Tables):
-
The above steps grant access to existing tables. To automatically grant read access to new tables created in the 'public' schema, use:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
-
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'your_strong_password';
GRANT CONNECT ON DATABASE my_database TO readonly_user;
Granting Schema and Read Permissions:
\c my_database -- Connect to the specific database
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;
Setting Default Privileges (Optional):
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
Explanation:
- Replace
'your_strong_password'
with a secure password for the user. - Replace
my_database
with the actual name of the database you want to grant access to. - The
\c my_database
command connects you to the specific database for granting schema and read permissions.
-
Granting SELECT on Specific Tables:
Instead of granting access to all tables in a schema, you can be more granular and grant
SELECT
permission on specific tables the user needs to access. This might be useful if you have a large database and want to restrict access to sensitive data.GRANT SELECT ON TABLE table1, table2 TO readonly_user;
-
Roles with Limited Permissions:
PostgreSQL allows creating custom roles with pre-defined permissions. You can create a role specifically for read-only access and assign it to users. This approach involves some additional setup but can be helpful for managing user permissions more efficiently, especially if you have many read-only users.
Here's a basic example:
CREATE ROLE read_only_role; GRANT CONNECT ON DATABASE my_database TO read_only_role; GRANT USAGE ON SCHEMA public TO read_only_role; GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_role; GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only_role; GRANT read_only_role TO readonly_user;
This first creates a role named
read_only_role
with the necessary permissions. Then, thereadonly_user
is assigned this role, inheriting its permissions.
postgresql