StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<h2>Grant usage/select to a single table</h2> <p>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:</p> <pre><code>GRANT CONNECT ON DATABASE mydb TO xxx; -- This assumes you're actually connected to mydb.. GRANT USAGE ON SCHEMA public TO xxx; GRANT SELECT ON mytable TO xxx; </code></pre> <h2>Multiple tables/views (PostgreSQL 9.0+)</h2> <p>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:</p> <pre><code>GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx; </code></pre> <p>This only affects tables that have already been created. More powerfully, you can automatically have <a href="http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html" rel="noreferrer">default roles assigned to new objects</a> in future:</p> <pre><code>ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO xxx; </code></pre> <p>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.</p> <h2>Multiple tables/views (PostgreSQL versions before 9.0)</h2> <p>To avoid errors in lengthy, multi-table changes, it is recommended to use the following 'automatic' process to generate the required <code>GRANT SELECT</code> to each table/view:</p> <pre><code>SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;' FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S'); </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload