How to make a query to the Postgres data dictionary to find out all the privileges that a particular user has.
I've been looking for a solution and I can not find anything. Thanks and good day
PostgreSQL: Show all the privileges for a concrete user
Asked Answered
Table permissions:
SELECT *
FROM information_schema.role_table_grants
WHERE grantee = 'YOUR_USER';
Ownership:
SELECT *
FROM pg_tables
WHERE tableowner = 'YOUR_USER';
Schema permissions:
SELECT r.usename AS grantor,
e.usename AS grantee,
nspname,
privilege_type,
is_grantable
FROM pg_namespace
JOIN LATERAL (SELECT *
FROM aclexplode(nspacl) AS x) a
ON true
JOIN pg_user e
ON a.grantee = e.usesysid
JOIN pg_user r
ON a.grantor = r.usesysid
WHERE e.usename = 'YOUR_USER';
Not sure what this does, but all query results are empty. Yet my user can access their own databases, tables and even peek into foreign databases and create/delete new tables there! This answer doesn't help. –
Traci
@ygoe: if a user owns a table (or database), no special grant exists as the owner of an object always has full access to that object (without grants) –
Panathenaea
Note:
JOIN LATERAL is available in Postgres from version 9.3 thus the schema permissions query above will not work in AWS Redshift. –
Astrophysics That seems all fine and good for database objects. How does one find the existing privs for a role to the databases themselves? thanks –
Mesocarp
would you mind, to spell out the display of SCHEMA privileges in more detail @Jeff? –
Hymenium
This Schema permissions query does not work well, particularly with AWS, because
pg_user only has users that can log in, so the joins fail when grants are made by roles which cannot log in, which is the usual case for AWS, and because it ignores permissions granted via PUBLIC. I suggest you use my query instead. –
Wegner This is what worked for me the best. short and clean.
\du lists all user accounts and roles and \du+ is the extended version which shows even more information.
# \du
List of roles
Role name | Attributes | Member of
--------------------+------------------------------------------------------------+-----------
padmin | Superuser, Create role, Create DB | {}
test | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
root | Superuser, Create role, Create DB | {}
# \du+
List of roles
Role name | Attributes | Member of | Description
--------------------+------------------------------------------------------------+-----------+-------------
padmin | Superuser, Create role, Create DB | {} |
test | | {} |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
root | Superuser, Create role, Create DB | {} |
This query shows attributes, the question asks for privileges. –
Deledda
This command was helpful for me:
\l
Here's how I used it:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------------------------+-----------------+----------+---------+-------+-------------------------------------
mydb1 | postgres | UTF8 | en_NG | en_NG | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | myuser=CTc/postgres
mydb2 | postgres | UTF8 | en_NG | en_NG | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | my_user=CTc/postgres
Resources: PostgreSQL: List the database privileges using psql
That's all.
I hope this helps
You can use this script to retrieve all priviliges for all PostgreSQL roles (server level, db ownership, object ownership, object permission, schema permissions). Add filters to get privileges for a specific user
WITH server_permissions AS (
SELECT
r.rolname,
'Server_Permissions' AS "Level",
r.rolsuper,
r.rolinherit,
r.rolcreaterole,
r.rolcreatedb,
r.rolcanlogin,
ARRAY(
SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON m.roleid = b.oid
WHERE m.member = r.oid
) AS memberof,
r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
),
db_ownership AS (
SELECT
r.rolname,
'DB_Ownership' AS "Level",
d.datname
FROM pg_catalog.pg_database d, pg_catalog.pg_roles r
WHERE d.datdba = r.oid
),
schema_permissions AS (
SELECT
'Schema Permissions' AS "Level",
r.rolname AS role_name,
nspname AS schema_name,
pg_catalog.has_schema_privilege(r.rolname, nspname, 'CREATE') AS create_grant,
pg_catalog.has_schema_privilege(r.rolname, nspname, 'USAGE') AS usage_grant
FROM pg_namespace pn, pg_catalog.pg_roles r
WHERE array_to_string(nspacl, ',') LIKE '%' || r.rolname || '%'
AND nspowner > 1
),
table_ownership AS (
SELECT
'Table Ownership' AS "Level",
tableowner,
schemaname,
tablename
FROM pg_tables
GROUP BY tableowner, schemaname, tablename
),
object_permissions AS (
SELECT
'Object Permissions' AS "Level",
COALESCE(NULLIF(s[1], ''), 'public') AS rolname,
n.nspname,
relname,
CASE
WHEN relkind = 'm' THEN 'Materialized View'
WHEN relkind = 'p' THEN 'Partitioned Table'
WHEN relkind = 'S' THEN 'Sequence'
WHEN relkind = 'I' THEN 'Partitioned Index'
WHEN relkind = 'v' THEN 'View'
WHEN relkind = 'i' THEN 'Index'
WHEN relkind = 'c' THEN 'Composite Type'
WHEN relkind = 't' THEN 'TOAST table'
WHEN relkind = 'r' THEN 'Table'
WHEN relkind = 'f' THEN 'Foreign Table'
END AS "Object Type",
s[2] AS privileges
FROM
pg_class c
JOIN pg_namespace n ON n.oid = relnamespace
JOIN pg_roles r ON r.oid = relowner,
UNNEST(COALESCE(relacl::text[], FORMAT('{%s=arwdDxt/%s}', rolname, rolname)::text[])) acl,
REGEXP_SPLIT_TO_ARRAY(acl, '=|/') s
WHERE relkind <> 'i' AND relkind <> 't'
)
SELECT
"Level",
rolname AS "Role",
'N/A' AS "Object Name",
'N/A' AS "Schema Name",
'N/A' AS "DB Name",
'N/A' AS "Object Type",
'N/A' AS "Privileges",
rolsuper::text AS "Is SuperUser",
rolinherit::text,
rolcreaterole::text,
rolcreatedb::text,
rolcanlogin::text,
memberof::text,
rolbypassrls::text
FROM server_permissions
UNION
SELECT
dow."Level",
dow.rolname,
'N/A',
'N/A',
datname,
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A'
FROM db_ownership AS dow
UNION
SELECT
"Level",
role_name,
'N/A',
schema_name,
'N/A',
'N/A',
CASE
WHEN create_grant IS TRUE AND usage_grant IS TRUE THEN 'Usage+Create'
WHEN create_grant IS TRUE AND usage_grant IS FALSE THEN 'Create'
WHEN create_grant IS FALSE AND usage_grant IS TRUE THEN 'Usage'
ELSE 'None'
END,
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A'
FROM schema_permissions
UNION
SELECT
"Level",
tableowner,
tablename,
schemaname,
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A'
FROM table_ownership
UNION
SELECT
"Level",
rolname,
relname,
nspname,
'N/A',
"Object Type",
privileges,
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A'
FROM object_permissions
ORDER BY "Role";
As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. –
Infantryman
The answer from Vao Tsun regarding how to see permissions for schemas is outdated and potentially hides a lot of results (because pg_user only has users that can log in, so the joins fail when the grantor or grantee is a role that cannot log in). Use this instead:
Show schema permissions (2023)
The following lists schemas and permissions for the current database.
WITH users AS (select rolname, oid
from pg_roles
union
select 'PUBLIC', 0)
SELECT r.rolname AS grantor,
e.rolname AS grantee,
nspname as schema,
privilege_type,
is_grantable
FROM pg_namespace,
aclexplode(nspacl) AS a
JOIN users AS e
ON a.grantee = e.oid
JOIN users AS r
ON a.grantor = r.oid
-- Add a WHERE clause to limit results to a single user
-- WHERE e.rolname = 'PUBLIC' or e.rolname = 'THE_ROLE_YOU_WANT'
Below are queries that return the direct privileges a role/user has been granted - and by that I mean:
GRANTs on/OWNERships of databases, tablespaces, parameters, schemas, tables (including the table-like views, materialized views, partitioned tables and foreign tables), table(-like) columns, sequences, functions, procedures, large objects, types (base, composite, enum, pseudo, range and multirange), domains, languages, foreign-data wrappers, and foreign servers
(Note there is no concept of OWNERnership of parameters, nor of table(-like) columns independent from each column's table)
GRANTs of membership to other roles
The ability to login, create roles, create databases, and if they're a superuser
But not including anything granted to the PUBLIC role
Notes:
- Because only direct privileges are returned, any privileges inherited from role membership are not included.
- Depending on the query, on a database with quite large catalog tables (at least for my world), for me they take between ~10ms and ~20ms.
- The privilege_type column contains PostgreSQL-known privilege types, with the addition of
MEMBER,OWNER,LOGIN,SUPERUSER,CREATE ROLEandCREATE DATABASE - Login, role-creation, database-creation and superuser status is cluster-wide - i.e. independant of the currently connected database
- Role memberships and permissions on databases, tablespaces and parameters are also cluster-wide
- Everything else returned is only for the currently connected database.
- Parameters and table(-like) columns have no concept of ownership.
- For permissions stored with the role, it uses
pg_roles. For role membership, it uses pg_auth_members. And for everything else, it goes via pg_shdepend to determine OWNERship, and lead to the pg_* catalog table in question that stores ACL-permissions. While this makes the query long, I think by going via pg_shdepend it can be more efficient when there are many rows in the catalog tables - avoiding full table scans of pg_class for example when it's very big.
Example output:
on | name | privilege_type
----------------------+-----------------------------------------+----------------
cluster | | LOGIN
role | other_role | MEMBER
database | new_database | OWNER
database | postgres | CONNECT
schema | my_schema | USAGE
table | my_schema.my_table | SELECT
sequence | my_schema.my_sequence | USAGE
table column | my_schema.my_table.id | SELECT
table column | my_view.id | SELECT
function | prod(double precision,double precision) | OWNER
aggregate function | incremented_max(integer) | EXECUTE
large object | 175076 | SELECT
composite type | my_schema.compfoo | OWNER
domain | my_schema.mydom | OWNER
composite type | my_schema.compfoo | USAGE
pseudo type | "any" | USAGE
language | sql | OWNER
language | sql | USAGE
tablespace | my_new_tablespace | OWNER
foreign-data wrapper | dummy | USAGE
foreign server | myserver | OWNER
parameter | max_connections | SET
And the query is:
-- Cluster permissions not "on" anything else
SELECT
'cluster' AS on,
NULL AS name,
unnest(
CASE WHEN rolcanlogin THEN ARRAY['LOGIN'] ELSE ARRAY[]::text[] END
|| CASE WHEN rolsuper THEN ARRAY['SUPERUSER'] ELSE ARRAY[]::text[] END
|| CASE WHEN rolcreaterole THEN ARRAY['CREATE ROLE'] ELSE ARRAY[]::text[] END
|| CASE WHEN rolcreatedb THEN ARRAY['CREATE DATABASE'] ELSE ARRAY[]::text[] END
) AS privilege_type
FROM pg_roles
WHERE oid = quote_ident('YOUR_ROLE')::regrole
UNION ALL
-- Direct role memberships
SELECT 'role' AS on, groups.rolname AS name, 'MEMBER' AS privilege_type
FROM pg_auth_members mg
INNER JOIN pg_roles groups ON groups.oid = mg.roleid
INNER JOIN pg_roles members ON members.oid = mg.member
WHERE members.rolname = 'YOUR_ROLE'
-- Direct ACL or ownerships
UNION ALL (
-- ACL or owned-by dependencies of the role - global or in the currently connected database
WITH owned_or_acl AS (
SELECT
refobjid, -- The referenced object: the role in this case
classid, -- The pg_class oid that the dependant object is in
objid, -- The oid of the dependant object in the table specified by classid
deptype, -- The dependency type: o==is owner, and might have acl, a==has acl and not owner
objsubid -- The 1-indexed column index for table column permissions. 0 otherwise.
FROM pg_shdepend
WHERE refobjid = quote_ident('YOUR_ROLE')::regrole
AND refclassid='pg_catalog.pg_authid'::regclass
AND deptype IN ('a', 'o')
AND (dbid = 0 OR dbid = (SELECT oid FROM pg_database WHERE datname = current_database()))
),
relkind_mapping(relkind, type) AS (
VALUES
('r', 'table'),
('v', 'view'),
('m', 'materialized view'),
('f', 'foreign table'),
('p', 'partitioned table'),
('S', 'sequence')
),
prokind_mapping(prokind, type) AS (
VALUES
('f', 'function'),
('p', 'procedure'),
('a', 'aggregate function'),
('w', 'window function')
),
typtype_mapping(typtype, type) AS (
VALUES
('b', 'base type'),
('c', 'composite type'),
('e', 'enum type'),
('p', 'pseudo type'),
('r', 'range type'),
('m', 'multirange type'),
('d', 'domain')
)
-- Database ownership
SELECT 'database' AS on, quote_ident(datname) AS name, 'OWNER' AS privilege_type
FROM pg_database d
INNER JOIN owned_or_acl a ON a.objid = d.oid
WHERE classid = 'pg_database'::regclass AND deptype = 'o'
UNION ALL
-- Database privileges
SELECT 'database' AS on, quote_ident(datname) AS name, privilege_type
FROM pg_database d
INNER JOIN owned_or_acl a ON a.objid = d.oid
CROSS JOIN aclexplode(COALESCE(d.datacl, acldefault('d', d.datdba)))
WHERE classid = 'pg_database'::regclass AND grantee = refobjid
UNION ALL
-- Schema ownership
SELECT 'schema' AS on, n.oid::regnamespace::text AS name, 'OWNER' AS privilege_type
FROM pg_namespace n
INNER JOIN owned_or_acl a ON a.objid = n.oid
WHERE classid = 'pg_namespace'::regclass AND deptype = 'o'
UNION ALL
-- Schema privileges
SELECT 'schema' AS on, n.oid::regnamespace::text AS name, privilege_type
FROM pg_namespace n
INNER JOIN owned_or_acl a ON a.objid = n.oid
CROSS JOIN aclexplode(COALESCE(n.nspacl, acldefault('n', n.nspowner)))
WHERE classid = 'pg_namespace'::regclass AND grantee = refobjid
UNION ALL
-- Table(-like) ownership
SELECT r.type AS on, c.oid::regclass::text AS name, 'OWNER' AS privilege_type
FROM pg_class c
INNER JOIN owned_or_acl a ON a.objid = c.oid
INNER JOIN relkind_mapping r ON r.relkind = c.relkind
WHERE classid = 'pg_class'::regclass AND deptype = 'o' AND objsubid = 0
UNION ALL
-- Table(-like) privileges
SELECT r.type AS on, c.oid::regclass::text AS name, privilege_type
FROM pg_class c
INNER JOIN owned_or_acl a ON a.objid = c.oid
CROSS JOIN aclexplode(COALESCE(c.relacl, acldefault('r', c.relowner)))
INNER JOIN relkind_mapping r ON r.relkind = c.relkind
WHERE classid = 'pg_class'::regclass AND grantee = refobjid AND objsubid = 0
UNION ALL
-- Column privileges
SELECT 'table column', t.attrelid::regclass::text || '.' || quote_ident(attname) AS name, privilege_type
FROM pg_attribute t
INNER JOIN pg_class c ON c.oid = t.attrelid
INNER JOIN owned_or_acl a ON a.objid = t.attrelid
CROSS JOIN aclexplode(COALESCE(t.attacl, acldefault('c', c.relowner)))
WHERE classid = 'pg_class'::regclass AND grantee = refobjid AND objsubid != 0
UNION ALL
-- Function and procdedure ownership
SELECT m.type AS on, p.oid::regprocedure::text AS name, 'OWNER' AS privilege_type
FROM pg_proc p
INNER JOIN owned_or_acl a ON a.objid = p.oid
INNER JOIN prokind_mapping m ON m.prokind = p.prokind
WHERE classid = 'pg_proc'::regclass AND deptype = 'o'
UNION ALL
-- Function and procedure privileges
SELECT m.type AS on, p.oid::regprocedure::text AS name, privilege_type
FROM pg_proc p
INNER JOIN owned_or_acl a ON a.objid = p.oid
CROSS JOIN aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner)))
INNER JOIN prokind_mapping m ON m.prokind = p.prokind
WHERE classid = 'pg_proc'::regclass AND grantee = refobjid
UNION ALL
-- Large object ownership
SELECT 'large object' AS on, l.oid::text AS name, 'OWNER' AS privilege_type
FROM pg_largeobject_metadata l
INNER JOIN owned_or_acl a ON a.objid = l.oid
WHERE classid = 'pg_largeobject'::regclass AND deptype = 'o'
UNION ALL
-- Large object privileges
SELECT 'large object' AS on, l.oid::text AS name, privilege_type
FROM pg_largeobject_metadata l
INNER JOIN owned_or_acl a ON a.objid = l.oid
CROSS JOIN aclexplode(COALESCE(l.lomacl, acldefault('L', l.lomowner)))
WHERE classid = 'pg_largeobject'::regclass AND grantee = refobjid
UNION ALL
-- Type ownership
SELECT m.type, t.oid::regtype::text AS name, 'OWNER' AS privilege_type
FROM pg_type t
INNER JOIN owned_or_acl a ON a.objid = t.oid
INNER JOIN typtype_mapping m ON m.typtype = t.typtype
WHERE classid = 'pg_type'::regclass AND deptype = 'o'
UNION ALL
-- Type privileges
SELECT m.type, t.oid::regtype::text AS name, privilege_type
FROM pg_type t
INNER JOIN owned_or_acl a ON a.objid = t.oid
CROSS JOIN aclexplode(COALESCE(t.typacl, acldefault('T', t.typowner)))
INNER JOIN typtype_mapping m ON m.typtype = t.typtype
WHERE classid = 'pg_type'::regclass AND grantee = refobjid
UNION ALL
-- Language ownership
SELECT 'language' AS on, quote_ident(l.lanname) AS name, 'OWNER' AS privilege_type
FROM pg_language l
INNER JOIN owned_or_acl a ON a.objid = l.oid
WHERE classid = 'pg_language'::regclass AND deptype = 'o'
UNION ALL
-- Language privileges
SELECT 'language' AS on, quote_ident(l.lanname) AS name, privilege_type
FROM pg_language l
INNER JOIN owned_or_acl a ON a.objid = l.oid
CROSS JOIN aclexplode(COALESCE(l.lanacl, acldefault('l', l.lanowner)))
WHERE classid = 'pg_language'::regclass AND grantee = refobjid
UNION ALL
-- Tablespace ownership
SELECT 'tablespace' AS on, quote_ident(t.spcname) AS name, 'OWNER' AS privilege_type
FROM pg_tablespace t
INNER JOIN owned_or_acl a ON a.objid = t.oid
WHERE classid = 'pg_tablespace'::regclass AND deptype = 'o'
UNION ALL
-- Tablespace privileges
SELECT 'tablespace' AS on, quote_ident(t.spcname) AS name, privilege_type
FROM pg_tablespace t
INNER JOIN owned_or_acl a ON a.objid = t.oid
CROSS JOIN aclexplode(COALESCE(t.spcacl, acldefault('t', t.spcowner)))
WHERE classid = 'pg_tablespace'::regclass AND grantee = refobjid
UNION ALL
-- Foreign data wrapper ownership
SELECT 'foreign-data wrapper' AS on, quote_ident(f.fdwname) AS name, 'OWNER' AS privilege_type
FROM pg_foreign_data_wrapper f
INNER JOIN owned_or_acl a ON a.objid = f.oid
WHERE classid = 'pg_foreign_data_wrapper'::regclass AND deptype = 'o'
UNION ALL
-- Foreign data wrapper privileges
SELECT 'foreign-data wrapper' AS on, quote_ident(f.fdwname) AS name, privilege_type
FROM pg_foreign_data_wrapper f
INNER JOIN owned_or_acl a ON a.objid = f.oid
CROSS JOIN aclexplode(COALESCE(f.fdwacl, acldefault('F', f.fdwowner)))
WHERE classid = 'pg_foreign_data_wrapper'::regclass AND grantee = refobjid
UNION ALL
-- Foreign server ownership
SELECT 'foreign server' AS on, quote_ident(f.srvname) AS name, 'OWNER' AS privilege_type
FROM pg_foreign_server f
INNER JOIN owned_or_acl a ON a.objid = f.oid
WHERE classid = 'pg_foreign_server'::regclass AND deptype = 'o'
UNION ALL
-- Foreign server privileges
SELECT 'foreign server' AS on, quote_ident(f.srvname) AS name, privilege_type
FROM pg_foreign_server f
INNER JOIN owned_or_acl a ON a.objid = f.oid
CROSS JOIN aclexplode(COALESCE(f.srvacl, acldefault('S', f.srvowner)))
WHERE classid = 'pg_foreign_server'::regclass AND grantee = refobjid
UNION ALL
-- Parameter privileges
SELECT 'parameter' AS on, quote_ident(p.parname) AS name, privilege_type
FROM pg_parameter_acl p
INNER JOIN owned_or_acl a ON a.objid = p.oid
CROSS JOIN aclexplode(p.paracl)
WHERE classid = 'pg_parameter_acl'::regclass AND grantee = refobjid
);
Or if you want slightly more structured output that can more easily be used (once safely quoted/escaped) in further SQL to manage permisisons:
on | name_1 | name_2 | name_3 | privilege_type
----------------------+-------------------+-----------------+---------+----------------
cluster | | | | LOGIN
role | other_role | | | MEMBER
database | new_database | | | OWNER
database | postgres | | | CONNECT
schema | my_schema | | | USAGE
table | my_schema | my_table | | SELECT
sequence | my_schema | my_sequence | | USAGE
table column | my_schema | my_table | id | SELECT
table column | public | my_view | id | SELECT
function | public | prod | 32754 | OWNER
aggregate function | public | incremented_max | integer | EXECUTE
large object | 175076 | | | SELECT
composite type | my_schema | compfoo | | OWNER
domain | my_schema | mydom | | OWNER
composite type | my_schema | compfoo | | USAGE
pseudo type | pg_catalog | any | | USAGE
language | sql | | | OWNER
language | sql | | | USAGE
tablespace | my_new_tablespace | | | OWNER
foreign-data wrapper | dummy | | | USAGE
foreign server | myserver | | | OWNER
parameter | max_connections | | | SET
You can use the more complex (and a touch slower) query:
-- Cluster permissions not "on" anything else
SELECT
'cluster' AS on,
NULL AS name_1,
NULL AS name_2,
NULL AS name_3,
unnest(
CASE WHEN rolcanlogin THEN ARRAY['LOGIN'] ELSE ARRAY[]::text[] END
|| CASE WHEN rolsuper THEN ARRAY['SUPERUSER'] ELSE ARRAY[]::text[] END
|| CASE WHEN rolcreaterole THEN ARRAY['CREATE ROLE'] ELSE ARRAY[]::text[] END
|| CASE WHEN rolcreatedb THEN ARRAY['CREATE DATABASE'] ELSE ARRAY[]::text[] END
) AS privilege_type
FROM pg_roles
WHERE oid = quote_ident('manual_test')::regrole
UNION ALL
-- Direct role memberships
SELECT 'role' AS on, groups.rolname AS name_1, NULL AS name_2, NULL AS name_3, 'MEMBER' AS privilege_type
FROM pg_auth_members mg
INNER JOIN pg_roles groups ON groups.oid = mg.roleid
INNER JOIN pg_roles members ON members.oid = mg.member
WHERE members.rolname = 'manual_test'
-- Direct ACL or ownerships
UNION ALL (
-- ACL or owned-by dependencies of the role - global or in the currently connected database
WITH owned_or_acl AS (
SELECT
refobjid, -- The referenced object: the role in this case
classid, -- The pg_class oid that the dependant object is in
objid, -- The oid of the dependant object in the table specified by classid
deptype, -- The dependency type: o==is owner, and might have acl, a==has acl and not owner
objsubid -- The 1-indexed column index for table column permissions. 0 otherwise.
FROM pg_shdepend
WHERE refobjid = quote_ident('manual_test')::regrole
AND refclassid='pg_catalog.pg_authid'::regclass
AND deptype IN ('a', 'o')
AND (dbid = 0 OR dbid = (SELECT oid FROM pg_database WHERE datname = current_database()))
),
relkind_mapping(relkind, type) AS (
VALUES
('r', 'table'),
('v', 'view'),
('m', 'materialized view'),
('f', 'foreign table'),
('p', 'partitioned table'),
('S', 'sequence')
),
prokind_mapping(prokind, type) AS (
VALUES
('f', 'function'),
('p', 'procedure'),
('a', 'aggregate function'),
('w', 'window function')
),
typtype_mapping(typtype, type) AS (
VALUES
('b', 'base type'),
('c', 'composite type'),
('e', 'enum type'),
('p', 'pseudo type'),
('r', 'range type'),
('m', 'multirange type'),
('d', 'domain')
)
-- Database ownership
SELECT 'database' AS on, datname AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
FROM pg_database d
INNER JOIN owned_or_acl a ON a.objid = d.oid
WHERE classid = 'pg_database'::regclass AND deptype = 'o'
UNION ALL
-- Database privileges
SELECT 'database' AS on, datname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
FROM pg_database d
INNER JOIN owned_or_acl a ON a.objid = d.oid
CROSS JOIN aclexplode(COALESCE(d.datacl, acldefault('d', d.datdba)))
WHERE classid = 'pg_database'::regclass AND grantee = refobjid
UNION ALL
-- Schema ownership
SELECT 'schema' AS on, nspname AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
FROM pg_namespace n
INNER JOIN owned_or_acl a ON a.objid = n.oid
WHERE classid = 'pg_namespace'::regclass AND deptype = 'o'
UNION ALL
-- Schema privileges
SELECT 'schema' AS on, nspname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
FROM pg_namespace n
INNER JOIN owned_or_acl a ON a.objid = n.oid
CROSS JOIN aclexplode(COALESCE(n.nspacl, acldefault('n', n.nspowner)))
WHERE classid = 'pg_namespace'::regclass AND grantee = refobjid
UNION ALL
-- Table(-like) ownership
SELECT r.type AS on, nspname AS name_1, relname AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN owned_or_acl a ON a.objid = c.oid
INNER JOIN relkind_mapping r ON r.relkind = c.relkind
WHERE classid = 'pg_class'::regclass AND deptype = 'o' AND objsubid = 0
UNION ALL
-- Table(-like) privileges
SELECT r.type AS on, nspname AS name_1, relname AS name_2, NULL AS name_3, privilege_type
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN owned_or_acl a ON a.objid = c.oid
CROSS JOIN aclexplode(COALESCE(c.relacl, acldefault('r', c.relowner)))
INNER JOIN relkind_mapping r ON r.relkind = c.relkind
WHERE classid = 'pg_class'::regclass AND grantee = refobjid AND objsubid = 0
UNION ALL
-- Column privileges
SELECT 'table column', nspname AS name_1, relname AS name_2, attname AS name_3, privilege_type
FROM pg_attribute t
INNER JOIN pg_class c ON c.oid = t.attrelid
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN owned_or_acl a ON a.objid = t.attrelid
CROSS JOIN aclexplode(COALESCE(t.attacl, acldefault('c', c.relowner)))
WHERE classid = 'pg_class'::regclass AND grantee = refobjid AND objsubid != 0
UNION ALL
-- Function and procdedure ownership
SELECT m.type AS on, nspname AS name_1, proname AS name_2, p.oid::text AS name_3, 'OWNER' AS privilege_type
FROM pg_proc p
INNER JOIN pg_namespace n ON n.oid = p.pronamespace
INNER JOIN owned_or_acl a ON a.objid = p.oid
INNER JOIN prokind_mapping m ON m.prokind = p.prokind
WHERE classid = 'pg_proc'::regclass AND deptype = 'o'
UNION ALL
-- Function and procedure privileges
SELECT m.type AS on, nspname AS name_1, proname AS name_2, p.oid::text AS name_3, privilege_type
FROM pg_proc p
INNER JOIN pg_namespace n ON n.oid = p.pronamespace
INNER JOIN owned_or_acl a ON a.objid = p.oid
CROSS JOIN aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner)))
INNER JOIN prokind_mapping m ON m.prokind = p.prokind
WHERE classid = 'pg_proc'::regclass AND grantee = refobjid
UNION ALL
-- Large object ownership
SELECT 'large object' AS on, l.oid::text AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
FROM pg_largeobject_metadata l
INNER JOIN owned_or_acl a ON a.objid = l.oid
WHERE classid = 'pg_largeobject'::regclass AND deptype = 'o'
UNION ALL
-- Large object privileges
SELECT 'large object' AS on, l.oid::text AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
FROM pg_largeobject_metadata l
INNER JOIN owned_or_acl a ON a.objid = l.oid
CROSS JOIN aclexplode(COALESCE(l.lomacl, acldefault('L', l.lomowner)))
WHERE classid = 'pg_largeobject'::regclass AND grantee = refobjid
UNION ALL
-- Type ownership
SELECT m.type, nspname AS name_1, typname AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
FROM pg_type t
INNER JOIN pg_namespace n ON n.oid = t.typnamespace
INNER JOIN owned_or_acl a ON a.objid = t.oid
INNER JOIN typtype_mapping m ON m.typtype = t.typtype
WHERE classid = 'pg_type'::regclass AND deptype = 'o'
UNION ALL
-- Type privileges
SELECT m.type, nspname AS name_1, typname AS name_2, NULL AS name_3, privilege_type
FROM pg_type t
INNER JOIN pg_namespace n ON n.oid = t.typnamespace
INNER JOIN owned_or_acl a ON a.objid = t.oid
CROSS JOIN aclexplode(COALESCE(t.typacl, acldefault('T', t.typowner)))
INNER JOIN typtype_mapping m ON m.typtype = t.typtype
WHERE classid = 'pg_type'::regclass AND grantee = refobjid
UNION ALL
-- Language ownership
SELECT 'language' AS on, l.lanname AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
FROM pg_language l
INNER JOIN owned_or_acl a ON a.objid = l.oid
WHERE classid = 'pg_language'::regclass AND deptype = 'o'
UNION ALL
-- Language privileges
SELECT 'language' AS on, l.lanname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
FROM pg_language l
INNER JOIN owned_or_acl a ON a.objid = l.oid
CROSS JOIN aclexplode(COALESCE(l.lanacl, acldefault('l', l.lanowner)))
WHERE classid = 'pg_language'::regclass AND grantee = refobjid
UNION ALL
-- Tablespace ownership
SELECT 'tablespace' AS on, t.spcname AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
FROM pg_tablespace t
INNER JOIN owned_or_acl a ON a.objid = t.oid
WHERE classid = 'pg_tablespace'::regclass AND deptype = 'o'
UNION ALL
-- Tablespace privileges
SELECT 'tablespace' AS on, t.spcname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
FROM pg_tablespace t
INNER JOIN owned_or_acl a ON a.objid = t.oid
CROSS JOIN aclexplode(COALESCE(t.spcacl, acldefault('t', t.spcowner)))
WHERE classid = 'pg_tablespace'::regclass AND grantee = refobjid
UNION ALL
-- Foreign data wrapper ownership
SELECT 'foreign-data wrapper' AS on, f.fdwname AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
FROM pg_foreign_data_wrapper f
INNER JOIN owned_or_acl a ON a.objid = f.oid
WHERE classid = 'pg_foreign_data_wrapper'::regclass AND deptype = 'o'
UNION ALL
-- Foreign data wrapper privileges
SELECT 'foreign-data wrapper' AS on, f.fdwname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
FROM pg_foreign_data_wrapper f
INNER JOIN owned_or_acl a ON a.objid = f.oid
CROSS JOIN aclexplode(COALESCE(f.fdwacl, acldefault('F', f.fdwowner)))
WHERE classid = 'pg_foreign_data_wrapper'::regclass AND grantee = refobjid
UNION ALL
-- Foreign server ownership
SELECT 'foreign server' AS on, f.srvname AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
FROM pg_foreign_server f
INNER JOIN owned_or_acl a ON a.objid = f.oid
WHERE classid = 'pg_foreign_server'::regclass AND deptype = 'o'
UNION ALL
-- Foreign server privileges
SELECT 'foreign server' AS on, f.srvname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
FROM pg_foreign_server f
INNER JOIN owned_or_acl a ON a.objid = f.oid
CROSS JOIN aclexplode(COALESCE(f.srvacl, acldefault('S', f.srvowner)))
WHERE classid = 'pg_foreign_server'::regclass AND grantee = refobjid
UNION ALL
-- Parameter privileges
SELECT 'parameter' AS on, p.parname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
FROM pg_parameter_acl p
INNER JOIN owned_or_acl a ON a.objid = p.oid
CROSS JOIN aclexplode(p.paracl)
WHERE classid = 'pg_parameter_acl'::regclass AND grantee = refobjid
);
Answer from 2k24: there is \dp concrete_TABLE - but it works "other way around" - get's you privileges by table (view, sequence), not by user:
\dp [PATTERN] list table, view, and sequence access privileges
Usage:
=> \dp some_things
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------+-------+---------------------------+-------------------+----------
public | some_things | table | dba=arwdDxt/main-db +| |
| | | metabase=r/main-db +| |
| | | jane_dou=arwdDxt/main-db | |
(1 row)
You can also just use this to see if your user has anything other than SELECT
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username' AND with_hierarchy = 'YES'
© 2022 - 2025 — McMap. All rights reserved.
... FROM pg_namespace, aclexplode(nspacl) AS a ...– Lilililia