Posted By: Anonymous
I’d like to create a user in PostgreSQL that can only do SELECTs from a particular database. In MySQL the command would be:
GRANT SELECT ON mydb.* TO '✘✘✘'@'%' IDENTIFIED BY 'yyy';
What is the equivalent command or series of commands in PostgreSQL?
postgres=# CREATE ROLE ✘✘✘ LOGIN PASSWORD 'yyy'; postgres=# GRANT SELECT ON DATABASE mydb TO ✘✘✘;
But it appears that the only things you can grant on a database are CREATE, CONNECT, TEMPORARY, and TEMP.
Grant usage/select to a single table
If you only grant CONNECT to a database, the user can connect but has no other privileges. You have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually like so:
GRANT CONNECT ON DATABASE mydb TO ✘✘✘; -- This assumes you're actually connected to mydb.. GRANT USAGE ON SCHEMA public TO ✘✘✘; GRANT SELECT ON mytable TO ✘✘✘;
Multiple tables/views (PostgreSQL 9.0+)
In the latest versions of PostgreSQL, you can grant permissions on all tables/views/etc in the schema using a single command rather than having to type them one by one:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ✘✘✘;
This only affects tables that have already been created. More powerfully, you can automatically have default roles assigned to new objects in future:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ✘✘✘;
Note that by default this will only affect objects (tables) created by the user that issued this command: although it can also be set on any role that the issuing user is a member of. However, you don’t pick up default privileges for all roles you’re a member of when creating new objects… so there’s still some faffing around. If you adopt the approach that a database has an owning role, and schema changes are performed as that owning role, then you should assign default privileges to that owning role. IMHO this is all a bit confusing and you may need to experiment to come up with a functional workflow.
Multiple tables/views (PostgreSQL versions before 9.0)
To avoid errors in lengthy, multi-table changes, it is recommended to use the following ‘automatic’ process to generate the required
GRANT SELECT to each table/view:
SELECT 'GRANT SELECT ON ' || relname || ' TO ✘✘✘;' FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');
This should output the relevant GRANT commands to GRANT SELECT on all tables, views, and sequences in public, for copy-n-paste love. Naturally, this will only be applied to tables that have already been created.