How to create a user with readonly privileges for all databases in Postgresql?
Solution 1
You cannot do this on database level, only on schema level.
Assuming you are only using the public
schema in each database, you can do this:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user;
Solution 2
You need to do 2 things: firstly, allow access to existing objects; and secondly, set the default access for new objects created from now on.
Note that granting access to "TABLES" includes views, but does not include sequences (such as the auto-increment function for "SERIAL" columns), so you'll probably want to grant access to those as well.
The below assumes you want to do everything in the public
schema. The ALTER DEFAULT PRIVILEGES
statement can act on the entire database by omitting the IN SCHEMA ...
clause; the GRANT
has to be run once for each schema.
-- Grant access to current tables and views
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1;
-- Now make sure that's also available on new tables and views by default
ALTER DEFAULT PRIVILEGES
IN SCHEMA public -- omit this line to make a default across all schemas
GRANT SELECT
ON TABLES
TO user1;
-- Now do the same for sequences
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO user1;
ALTER DEFAULT PRIVILEGES
IN SCHEMA public -- omit this line to make a default across all schemas
GRANT SELECT, USAGE
ON SEQUENCES
TO user1;
PostgreSQL manual
- http://www.postgresql.org/docs/current/interactive/sql-grant.html
- http://www.postgresql.org/docs/current/interactive/sql-alterdefaultprivileges.html
Solution 3
I realize you've already said this isn't an acceptable answer, but it's the right answer anyway.
Specifying security (GRANT and REVOKE) is part of table design and testing.
Don't move tables to production before table definitions, security, tests, and test data are under version control.
Having said that, PostgreSQL doesn't have any SELECT permissions on databases. You can grant only CREATE, CONNECT, or TEMP permissions on databases.
You can grant SELECT on all tables in a given schema. I don't know how that affects tables created after running the GRANT statement, but it's fairly easy to test.
Solution 4
For Postgres versions lower than 9.0:
psql -d DBNAME -qAt -c "SELECT 'GRANT SELECT ON ' || tablename || ' TO USER;'
FROM pg_tables WHERE schemaname = 'public'" | psql -d DBNAME
psql -d DBNAME -qAt -c "SELECT 'GRANT SELECT ON ' || viewname || ' TO USER;'
FROM pg_views WHERE schemaname = 'public'" | psql -d DBNAME
psql -d DBNAME -qAt -c "SELECT 'GRANT SELECT ON ' || relname || ' TO USER;'
FROM pg_statio_all_sequences WHERE schemaname = 'public'" | psql -d DBNAME
Solution 5
I do the next steps for create read-only user:
create your_user:
1. createuser --interactive --pwprompt
go to postgresql in your_databases:
2. psql your_database
define access privileges:
3. GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user;
define default access privileges:
4. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO your_user;
Alptugay
Updated on June 01, 2021Comments
-
Alptugay about 3 years
I want to create a user with only select privilege for all tables in all databases. I thought that I could get a list of databases and apply the following command for each database:
GRANT select ON DATABASE dbname to user1;
But I got the following error:
ERROR: invalid privilege type SELECT for database
When I googled people advised to do the
grant select
operation for all tables. But new tables are being added always. So this is not an acceptable solution for me. Does anyone know any workarounds? -
IMSoP over 12 yearsYour statement that this will apply to newly created tables and views is incorrect - the "ALL TABLES" is simply short-hand for running a GRANT SELECT on each currently existing table, and does not set any defaults. To set the default for new objects, you need the ALTER DEFAULT PRIVILEGES command: postgresql.org/docs/current/static/… (See my answer below)
-
Sam Saffron over 11 yearsoddly enough I needed
grant usage on schema public to user1;
as well -
Michael almost 11 yearsThe original question was on the topic of SELECt for all tables and for ALL databases. This is possible in mysql but (i think) not possible in postgres. In postgres one has to grant these rights for each database independently. Maybe you can add this as a clarification (or correct me if it is possible).
-
a_horse_with_no_name almost 11 years@Michael: hmm, I thought the first sentence "you cannot do this on database level" pretty much says that. You have to do it per schema, you can not do it per database (or installation wide)
-
Michael almost 11 yearsyes - if you know postgres. My guess (as is true for me) is a lot of people with limited knowledge of pg will find this Q&A. Not everybody will know the schema is database specific. Plus if you simply connect via
psql
you can issue above commands without an error -> this lead me to the assumption the GRANT was applied installation wide :). Maybe simply add thepsql database_name
as a first command. On the other hand these comments will propably be sufficent for others :) -
tymik over 7 yearsi had to do
psql -d DBNAME -qAt -c "SELECT 'GRANT SELECT ON ' || relname || ' TO USER;' FROM pg_statio_all_tables WHERE schemaname = 'public'" | psql -d DBNAME
yet -
jerrymouse over 2 yearsIt is important to connect to the right DB first
\c mydatabase;
.user1
will only get access tomydatabase
.