Postgresql – How to create readonly user for backups in PostgreSQL

backuppermissionspostgresql-9.1

Is it true that it is IMPOSSIBLE to create a readonly backup user in PostgreSQL?

I've been advised on an IRC channel that you simply can't have a backup only user with no ownership privileges. I find it very strange so I want to make sure I'm not missing something.

Below is what I tried but it doesn't give me the results I'm looking for. When I do pg_dump on a given table I'm getting Permission denied for relation...:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup; 
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO backup;

Any help would be greatly appreciated!

Best Answer

No, it's easy (now anyway).

Grant the connect permission on a new user

GRANT CONNECT ON DATABASE mydb TO myReadolyUser;

Grant the permissions on all the current database objects. This is schema-specific, and you'll have to run one copy for every schema you wish for your user to use,
```
GRANT SELECT ON ALL TABLES IN SCHEMA mySchema TO myReadonlyUser;
```
From the docs, ALL TABLES includes everything you'd want.

There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported only for tables, sequences, and functions (but note that ALL TABLES is considered to include views and foreign tables.

Then ALTER DEFAULT PRIVLEGES to grant future SELECT privileges for objects not yet created.

ALTER DEFAULT PRIVILEGES IN SCHEMA mySchema
GRANT SELECT ON TABLES TO myReadonlyUser;

Related Solutions

PostgreSQL – Why New User Can Create Table

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation:

REVOKE ALL ON schema public FROM public;

Edit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. Assuming a non-superuser foo_user should be granted this privilege, this should be done with:

GRANT ALL ON schema public TO foo_user;

To know what ALL means for a schema, we must refer to GRANT in the doc, (in PG 9.2 there are no less than 14 forms of GRANT statements that apply to different things...). It appears that for a schema it means CREATE and USAGE.

On the other hand, GRANT ALL PRIVILEGES ON DATABASE... will grant CONNECT and CREATE and TEMP, but CREATE in this context relates to schemas, not permanent tables.

Regarding this error: ERROR: no schema has been selected to create in, it happens when trying to create an object without schema qualification (as in create table foo(...)) while lacking the permission to create it in any schema of the search_path.

PostgreSQL Permissions – Unable to Use Views After Restoring Data

To troubleshoot, I would start by looking at the output of SELECT current_user; and SHOW search_path;. Also, the output of \dp termin in psql.

My suspicion here is that you are stumbling over mixed case identifiers. The error message mentions a table

Map

Notice the capital letter M. Are you sure there is no mix-up with another table named map (lower case)?
Are PostgreSQL column names case-sensitive?

(Or, in another schema coming earlier in the search_path?)

Best Answer

Related Solutions

PostgreSQL – Why New User Can Create Table

PostgreSQL Permissions – Unable to Use Views After Restoring Data

Related Question