PostgreSQL and read-only users - the easiest way
Here you have the quick recipe:
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
\connect mydatabase
GRANT USAGE ON SCHEMA public to readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
CREATE USER myuser1 WITH PASSWORD 'secret_passwd';
GRANT readonly TO myuser1;
The deep dive(?)
Let's suppose you already have a well-securitized database with one or more users with the right privileges. If you still don't have your PostgreSQL database well protected, I recently wrote about how to securitize your database access well. You might find it useful.
Okay, so imagine someone asking you to create a read-only user to let an external provider connect and consume data, exporting reports, or whatever.
As we won't want to repeat all this process each time we need to create a read-only user, we'll create a role and then assign it.
So, for creating the read-only role, just type the following:
CREATE ROLE readonly;
We want this role to be able to connect to our database, so:
GRANT CONNECT ON DATABASE mydatabase TO readonly;
But, as you might know, this permission is not enough; we need to allow it to allow access to objects in the public schema.
\connect mydatabase
GRANT USAGE ON SCHEMA public to readonly;
As we may want to allow it to access all tables in the database, we'll type:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
But, If you only want to give access to specific tables, then type:
GRANT SELECT ON table_name TO readonly;
Now we have the role ready to use, so we're going to create a user and add it as a member.
CREATE USER myuser1 WITH PASSWORD 'secret_passwd';
GRANT readonly TO myuser1;
And that's it! Next time you need to create a read-only user it'll be a quick done task ;-)
If you have any question, doubt, or any improvement, feel free to let me know in this Twitter thread :)
If you found it useful, coffee is always appreciated. Thanks in advance!