-- Create a group | |
CREATE ROLE readaccess; | |
-- Grant access to existing tables | |
GRANT USAGE ON SCHEMA public TO readaccess; | |
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess; | |
-- Grant access to future tables | |
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess; | |
-- Create a final user with password | |
CREATE USER tomek WITH PASSWORD 'secret'; | |
GRANT readaccess TO tomek; |
This comment has been minimized.
This comment has been minimized.
Thank you very much! |
This comment has been minimized.
This comment has been minimized.
You are now connected to database "example" as user "user". Doesn't work. This worked for me: https://stackoverflow.com/questions/13497352/error-permission-denied-for-relation-tablename-on-postgres-while-trying-a-selec |
This comment has been minimized.
This comment has been minimized.
But with this role we are able to create tables individully. how to restrict user from creating tables individually? |
This comment has been minimized.
This comment has been minimized.
I too have same query - How to stop read only users from creating tables? |
This comment has been minimized.
This comment has been minimized.
It is allowed by default for every user to create tables in public schema. If you want to mitigate this, do the following:
|
This comment has been minimized.
This comment has been minimized.
Thank you, it's very useful |
This comment has been minimized.
This comment has been minimized.
Hi, thanks for your script. I work a lot with schemas, so i wrote a bash script that using your sql commands, echos shell commands to give read permissions on all schemas not just in public and it doesn't touch the database. Just paste generated lines on the shell to execute them against the real database. #!/bin/bash if [[ $# -eq 0 ]]; then if [[ $# -ne 2 ]]; then usage="select 'grant usage on schema ' || nspname ||' to $1;' from pg_catalog.pg_namespace;" echo "psql -t -c "$usage" $2 | psql $2" |
This comment has been minimized.
This comment has been minimized.
@electropolis were you granting on the correct database? were you connected when you did it? i noticed the same issue and found it was because i was granting for A database, not the correct one though |
This comment has been minimized.
This comment has been minimized.
AWSM! |
This comment has been minimized.
This comment has been minimized.
If you want this readonly user to use pg_dump, you may also need to grant access to sequences.
|
This comment has been minimized.
This comment has been minimized.
Hi there are four databases present in this server. When i create this user, that user can create tables in different databases. I want to restrict that too. |
This comment has been minimized.
This comment has been minimized.
Thank you very much, |
This comment has been minimized.
This comment has been minimized.
Thank you @tomek @slavafomin |
This comment has been minimized.
This comment has been minimized.
tks man |
This comment has been minimized.
This comment has been minimized.
CREATE ROLE readaccess; GRANT USAGE ON SCHEMA public TO readaccess; GRANT CONNECT ON DATABASE 'database_name' to readaccess; CREATE USER user_name WITH PASSWORD ‘password’; |
This comment has been minimized.
This comment has been minimized.
|
This comment has been minimized.
Thank you