Create PostgreSQL databases, schemas, and roles; grant and revoke privileges.
There is no PyPI package for this as I have released quite a few in the past only to discover months down the line that perhaps they are not as useful as they seemed to be. Use this at your own risk.
First blog post on the subject: https://notes.zilupe.com/posts/pg_objects/
A working Python example is in example.py
.
Command-line interface (CLI): python -m pg_objects.cli --help
CLI can be passed a JSON representation of the object graph and it will create all the managed objects for you.
This does not work on Amazon RDS for PostgreSQL because Amazon have bastardised the database and you don't have a super user. Don't use Amazon RDS for PostgreSQL unless you want to have a separate cluster for each use case and pay accordingly.
I have been trying to express PostgreSQL and Redshift permission objects declaratively for the last year and a half. This is roughly what I am after:
Objects:
- Type: User
Name: u
- Type: Database
Name: d
Owner: u
The greatest challenge so far has been to create and drop all the objects in the right order. Recently I realised that if the object dependencies are expressed in a graph then topological sort can be used to calculate the order of the operations. For create operations we process objects in topological order, and for drop operations we process them in reverse topological order.
Another important insight to help with organising the code was to express
the relationships between two objects as another object. For example, the fact that
a Database:d
is owned by User:u
is better expressed when behind the scenes
you introduce a separate object DatabaseOwner:d+u
. The dependencies then are:
Dependencies:
- Object: User:u
- Object: Database:d
DependsOn:
- User:u
- Object: DatabaseOwner:d+u
DependsOn:
- Database:d
- User:u
The separation of database from database ownership allows us to remove the owner of the database before attempting to drop the database or the user.
The topological order of the vertices of the above graph is:
TopologicalOrder:
- User:u
- Database:d
- DatabaseOwner:d+u
Another major problem is how privileges such as ON ALL TABLES
apply only to existing tables. For
tables created later one must ALTER DEFAULT PRIVILEGES
which require knowing in advance who is going
to create the objects (tables) to which these default privileges apply.
By default, the implicit PUBLIC
group has access to all databases. Default privileges
are per database, so there is no default privilege we can create to avoid this. For every newly
created database you have to revoke public group's access to the database.
REVOKE ALL PRIVILEGES ON DATABASE {self.name} FROM GROUP PUBLIC
- No grants -- all privileges to be managed by the super user which runs pg-objects.
- No user-specific privileges -- all privileges are group-specific
- All referenced groups, users, databases, and schemas should be managed -- an object is managed if the object graph contains an explicit declaration of the object.
ALL TABLES
privileges only
Unless specified otherwise, all users are created with NOINHERIT
which means that they
do not automatically inherit the privileges of groups they belong to. This means that
users are forced to call SET ROLE groupname
before using privileges associated
with a group. This makes managing default privileges and table ownership easier
as tables aren't created by individual users.
However, it seems that if user has NOINHERIT
, it is impossible for them to connect
to a database access to which is provided through group membership. This DOES NOT work:
postgres=> SET ROLE devops;
postgres=> \c devopsdb
FATAL: permission denied for database "devopsdb"
DETAIL: User does not have CONNECT privilege.
This forces us to declare CONNECT
privilege on individual users.