Testing Postgres DB for right permissions programmatically, after populating from .sql file

Question

I have a project where a postgres db is seeded with a .sql file in the ci test server. This will be build and published as a docker image.

Now, I won't be the only one pushing these .sql files to the server. So I need to check permissions of all postgres users (and maybe future users) against all databases, introduced by this file.

Example: User A and B. User A is a superuser and owns all databases. User B should only read be able to read in database X. and so on

Is there a way to test this programmaticaly? Other than 'try to read as B from Y -> error?

All other solutions I've found so far are either manually or 'try/error' ones.

Thanks. But: SELECT has_schema_privilege('B', 'X', 'select'); results in: ERROR: unrecognized privilege type: "select" Is there a way to ask "has_table_privilege" for select in all tables in the schema 'P' ? Is there a short way to exclude everything else? — MrEbbinghaus
– MrEbbinghaus, Commented Feb 6, 2017 at 15:21

Nick Barnes · Accepted Answer · 2017-02-07 02:31:28Z

You can inspect the grants for various types of database object via the access privilege inquiry functions. These all work at the level of individual objects, so for any kind of database-wide checks, you will need to combine them with catalog queries. You'll also need to consult the catalog for any ownership information. OID types are extremely helpful when writing these queries.

To check if user b has read-only access to all tables in schema p:

SELECT EVERY(
  has_table_privilege('b', oid, 'SELECT') AND
  NOT has_table_privilege('b', oid, 'INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER')
)
FROM pg_class
WHERE relkind = 'r'
  AND relnamespace = 'p'::regnamespace

(Note that table privileges are superseded by column-level grants, so you may want to check these too.)

To check if a is a superuser:

SELECT rolsuper
FROM pg_roles
WHERE rolname = 'a'

To check if a owns all databases (aside from those created by initdb):

SELECT EVERY(datdba = 'a'::regrole)
FROM pg_database
WHERE datname NOT IN ('postgres', 'template0', 'template1')

Collectives™ on Stack Overflow

Testing Postgres DB for right permissions programmatically, after populating from .sql file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related