Query Grants for a Table in Postgres

Query grants for a table in postgres

I already found it:

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='mytable'

Grant privileges to a specific table with PostgreSQL (relation * does not exist)

In order to specify permissions on a particular table, you have to be connected to the relevant database.
You can connect to a database using the \connect or the \c command.

The following work:

postgres=# \connect mydb
postgres=# GRANT SELECT ON TABLE mytable TO myuser;

Useful commands:

• Verify your current database: select current_database();

• Check the available relations: \d

Thanks @a_horse_with_no_name for pointing out the error.

Grant access for all all tables in Postgres

You would have to have the specific schema.
Also see this for further more usage on GRANT. Also, Grant all of the privileges available for the object's type. The PRIVILEGES key word is optional in PostgreSQL, though it is required by strict SQL.

So you can basically use all for a particular schema that all the tables belong to.

So

grant all on all tables in schema "schema_name" to user 

should do it for you but you need yo specify the schema name.

See this for more info:
link

Query to return user rights for each table with one right per result row

Like Islingre already suggested in the comments, you can use

select
  table_list.*,
  privilege,
  has_table_privilege(user_name, qualified_name, privilege) as setting
from
  table_list
  cross join user_list
  cross join (values
    ('delete'), ('insert'), ('references'), ('select'), ('trigger'), ('truncate'), ('update')
  ) as p(privilege)

Grant an automatic select to a all tables / PostgreSQL

You can set the default grants for newly created objects with the ALTER DEFAULT PRIVILEGES command, e.g.:

ALTER DEFAULT PRIVILEGES
FOR USER creator_role IN SCHEMA public
GRANT SELECT ON TABLES TO read_only;

As an aside, while you can't install triggers on the system catalogs, you can achieve the same thing (in Postgres 9.3+) with event triggers, which fire on any CREATE/DROP/ALTER statement.

Query GRANTS granted to a sequence in postgres

I've looked through the source code, and I can't find any place that exposes the ACL for sequences through the information_schema tables. (I could have missed something, though.)

PostgreSQL does expose the ACL for sequences in the system catalog pg_class.

SELECT relname, relacl
FROM pg_class
WHERE relkind = 'S'
  AND relacl is not null
  AND relnamespace IN (
      SELECT oid
      FROM pg_namespace
      WHERE nspname NOT LIKE 'pg_%'
        AND nspname != 'information_schema'
);

As far as the information_schema and standard SQL sequences go, PostgreSQL doesn't support them.

select feature_name, is_supported 
from information_schema.sql_features
where feature_name = 'Sequence generator support';

PostgreSQL is nonconforming in that respect, because it exposes information_schema.sequences without returning "YES" for 'Sequence generator support'. (That's an observation, not a criticism of PostgreSQL.)

But, having said all that, I couldn't find anything in the 2003 SQL standard that exposed those privileges, either. It's easy to find PRIVILEGE_TYPE in the definition of the ROLE_TABLE_GRANTS view, but there's nothing like that for sequences in the standard, as far as I can tell.

PostgreSQL: Show all the privileges for a concrete user

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'
;

Postgres. How to grant SELECT permissions automatically for new tables?

From the docs:

You can change default privileges only for objects that will be
created by yourself or by roles that you are a member of.

In other words, running ALTER DEFAULT PRIVILEGES as user postgres only affects tables created by postgres.

To change the defaults for another user's tables, you need to specify which user:

ALTER DEFAULT PRIVILEGES FOR ROLE clients_write ...

Note that the defaults are not inherited, so the target role is clients_write (i.e. the user actually running the CREATE TABLE command, who will become the new table's owner). Defaults for clients_write_role will have no effect unless your users SET ROLE clients_write_role; before creating a table.

---

Related Topics