In which we create users with no access to certain columns in PostgreSQL tables
Postgres is over 30 years old, not much younger than me. Which came as a surprise! Imagine what these things will look like when they're 80 years old. Will they wither away and fall into decay just like our bodies? Will they remain immortal, their bits floating forever in space? Or will they be something in the middle, like organizations, like civilizations, or cultures – with lifetime in millenia, but always changing, and always on the verge of extinction.
Why am I on the Postgres site though? Right, I want to create a user that can't see some of the columns of a table.
This will allow us to create restricted users that have read only access to non-critical parts of the database (e.g., for use in reporting scripts).
Postgres was born in Berkeley in 1986. Initially it had its own query language, but as it grew up, it learnt how to talk SQL, and this marriage caused it to change its name to PostgreSQL. But we'll keep calling it Postgres for now, like an old friend.
In the 30 years it has been around, it has grown to 1.5M lines of C (which averages to around 140 lines per day). More importantly for our purpose today, it has gained a robust access-control system, and column-level (and even row-level) security.
But user accounts in Postgres are a bit confusing too, though. My highest voted Stack Overflow answer is about Postgres user names, just to give a sense that it is not just me.
A part of the confusion arises because Postgres creates both a (Postgres) user account, and a database, with the same name as the name of the OS user account that started the server. And by default, the various Postgres CLI tools (like psql) will try to connect using the (Postgres) username and database name that is the same as the name OS user account running them. This is convenient, when one understands what's happening, but it is also confusing at first.
To be fair, conceptually it is quite nice. Postgres has a single concept of a "Role" to manage access control (instead of separate users and groups). Database objects like tables are owned by roles, and the owner can grant privileges (like SELECT, UPDATE) on those objects to other roles. Further, a role can be granted membership in another role, thus transitively granting it the privileges of the other role. So it is roles all the way down.
To keep it simple for now, let us create a new (Postgres) user, and a new database using that user (Remember, Postgres doesn't have users, only roles, but it is still convenient to call these as users in vernacular).
First, we connect to the running Postgres instance using our existing credentials. Once connected, we create a new role, and give that role permission to login, create databases and other roles. All this can be done with a single command.
$ psql -q -c 'CREATE ROLE myrole WITH LOGIN CREATEDB CREATEROLE;'
Now we reconnect, but this time using the new role, and then create a new database. Since we create this new database when we're connected using the new role, the new role will also be the owner of this new database.
$ psql --username myrole -q -c 'CREATE DATABASE mydb;'
Great, we have our playground setup. Let's reconnect to our test database using our test role, then the rest of the commands we can run interactively.
$ psql --username myrole --dbname mydb
Let's create a table with two columns, and insert a couple of rows.
mydb=> CREATE TABLE mytable (myint integer, mytext text);
CREATE TABLE
mydb=> INSERT INTO mytable VALUES (1, 'a');
INSERT 0 1
mydb=> INSERT INTO mytable VALUES (2, 'b');
INSERT 0 1
Now let us create a new role. We will try to restrict the privileges of this role.
mydb=> CREATE ROLE mylimitedrole WITH LOGIN;
CREATE ROLE
Without restarting psql, let us change the connection to use the new role.
mydb=> \c
You are now connected to database "mydb" as user "myrole".
mydb=> \c mydb mylimitedrole
You are now connected to database "mydb" as user "mylimitedrole".
And let's try to query mytable.
mydb=> SELECT * FROM mytable;
ERROR: permission denied for table mytable
Great actually. So the new role starts off without access to any of the existing data, which is a sane default. Let us grant it access to the table.
mydb=> \c mydb myrole
You are now connected to database "mydb" as user "myrole".
mydb=> GRANT SELECT ON mytable TO mylimitedrole;
GRANT
mydb=> \c mydb mylimitedrole
You are now connected to database "mydb" as user "mylimitedrole".
mydb=> SELECT * FROM mytable;
myint | mytext
-------+--------
1 | a
2 | b
(2 rows)
Cool. Notice how we granted just the SELECT privilege to mylimitedrole. Similarly, we could grant it INSERT, UPDATE, DELETE etc if needed.
However, what we wanted to do was to restrict access to the mytext column. To do this, we need to assign a more granular SELECT privilege, naming specific columns. Looking at the documentation, we might conjure up something like REVOKE SELECT (mytext) ON mytable FROM mylimitedrole;, however this won't work (and the documentation calls it out [6]).
Granting the privilege at the table level and then revoking it for one column will not do what one might wish: the table-level grant is unaffected by a column-level operation.
Irritatingly, the documentation doesn't tell us what we should do instead.
It seems then that the only way to achieve this is to go the other way around, and explicitly list out all the columns except the one we wish to exclude.
mydb=> \c mydb myrole
You are now connected to database "mydb" as user "myrole".
mydb=> REVOKE SELECT ON mytable FROM mylimitedrole;
REVOKE
mydb=> GRANT SELECT (myint) ON mytable TO mylimitedrole;
GRANT
mydb=> \c mydb mylimitedrole
You are now connected to database "mydb" as user "mylimitedrole".
mydb=> SELECT * FROM mytable;
ERROR: permission denied for table mytable
mydb=> SELECT myint FROM mytable;
myint
-------
1
2
(2 rows)
Looks like this does the job.
It would've been nice if we could've continued doing SELECT * and if the inaccessible columns would've been filtered out, but I guess that gets in the territory of SQL views. Which actually might be a simpler and more maintainable alternative for what we started off to achieve, since we can then grant permission to the entire view. But that's for another day.
Till then, happy SQueaLing! 🧑🔬
If you'd like to hear more about our experience with Postgres, follow us on Twitter.
Or if you'd hang out with a bunch of engineers building an e2ee photo storage service, come say hello on Discord.
References:
- https://www.postgresql.org/about/
- https://www.postgresql.org/docs/12/tutorial-createdb.html
- https://www.postgresql.org/docs/12/user-manag.html
- https://www.postgresql.org/docs/12/sql-createrole.html
- https://www.postgresql.org/docs/12/ddl-priv.html
- https://www.postgresql.org/docs/12/sql-grant.html
Cover image from: https://twitter.com/PostgreSQL/header_photo
Top comments (0)