PostgreSQL read only user

permissionspostgresqlpostgresql-10users

We have a PostgreSQL database 'db1', which is having around 500 schemas.
I am trying to create a read-only user for this particular PostgreSQL database.

I could find the following command for creating a user and granting permission schema wise

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO readonly_user;

but

1) We have around 500 schemas, so granting permission to each schema is difficult.

2) These schemas will be dynamically created or dropped on a daily basis, so giving permission at each time a schema is created is also a difficult task.

Is there any way to give the read-only permission for a whole database instead of schema?

In MySQL, I can do it using the following command

grant select on *.* to 'user_name'@'IP';

I am looking for a similar command in PostgreSQL

We are using PostgreSQL 10.

Best Answer

We have around 500 schemas, so granting permission to each schema is difficult.

No. It's tedious to do by hand, but it's also a prime candidate for automation, through scripting.

... dynamically created or dropped on a daily basis, so giving permission at each time a schema is created is also a difficult task.

I disagree. You're doing all the "difficult" stuff every day - dropping, recreating and repopulating all these schemas. I would suggest that adding this single statement to the end of that process is relatively trivial!

Is there any way to give the read-only permission for a whole database instead of schema?

Even if there is, you should not do so.

It sounds to me like these schemas are discrete entities (otherwise why not combine them into one?). Granting access across all of them strikes me as a Bad Idea.

Related Solutions

Sql-server – Permission in schema for user

From the documentation (GRANT Schema Permissions):

GRANT SELECT, DELETE -- , etc.
   ON SCHEMA::schema_name
   TO user_name;

For certain permissions they need to be granted at both the database level and the schema level. For example, you can give a user CREATE TABLE permissions at the database level, but that doesn't just allow them to create tables in any schema. Here is an example you can work through:

-- create a server-level SQL auth login
CREATE LOGIN DouglasExample WITH PASSWORD = 'x',
  CHECK_POLICY = OFF;

-- create an empty database and add a schema
CREATE DATABASE Douglas;
GO
USE Douglas;
GO
CREATE SCHEMA foo;
GO

-- create a user linked to the login
CREATE USER DouglasExample FROM LOGIN DouglasExample;
GO

-- grant the user the ability to create tables
GRANT CREATE TABLE TO DouglasExample;
GO

-- grant them explicit permissions on the schema only
GRANT SELECT, INSERT, DELETE, ALTER, EXECUTE, CONTROL
  ON SCHEMA::foo TO DouglasExample;
GO

-- now try to create tables in foo and in dbo
EXECUTE AS USER = N'DouglasExample';
GO
CREATE TABLE foo.what(id INT); -- succeeds
GO
CREATE TABLE dbo.who(id INT); -- fails, not in schema foo
-- CREATE TABLE who(id INT); would also fail with same error
-- unless DouglasExample had foo as their default_schema
GO
REVERT;
GO

The attempt in dbo fails with:

Msg 2760, Level 16, State 1
The specified schema name "dbo" either does not exist or you do not have permission to use it.

-- clean up:
USE master;
GO
DROP DATABASE Douglas;
GO
DROP LOGIN DouglasExample;

As a side note, please observe how important it is to always explicitly state the schema name when creating/referencing all objects.

PostgreSQL read-only user for pg_dumpall

Backups from a normal user are a nightmare. If you're using PostgreSQL on Linux, I'd recommend relying on sudo (and the /etc/sudoers file) to accomplish this, so that a given specific user can sudo -u postgres pg_dumpall <params> with specific parameters, so that's all they can do as user postgres (and therefore have no write access to the database).

Best Answer

Related Solutions

Sql-server – Permission in schema for user

PostgreSQL read-only user for pg_dumpall

Related Question