When I run \l+
in psql
to get a list of all databases and their access privileges, I see that some of the databases have blank access privileges. What does it mean when the Access privileges
column is blank in psql
?
If there is some sort of default, how can I see what that default is?
me=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-------------+-------------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
me | me | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7239 kB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7151 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7041 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7041 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
Another way to do this is to use the information_schema schema and query the table_privileges table as: $ SELECT * FROM information_schema. table_privileges LIMIT 5; The above query will show detailed information about user privileges on databases as well as tables.
PostgreSQL grants privileges on some types of objects to PUBLIC by default when the objects are created. No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, or tablespaces.
permissions - Created user can access all databases in PostgreSQL without any grants - Database Administrators Stack Exchange. Stack Overflow for Teams – Start collaborating and sharing organizational knowledge.
What does it mean when the Access privileges column is blank
Quote from the manual:
If the "Access privileges" column is empty for a given object, it means the object has default privileges (that is, its privileges column is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above
The default privileges are also documented there
PostgreSQL grants privileges on some types of objects to PUBLIC by default when the objects are created. No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, or tablespaces. For other types of objects, the default privileges granted to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) privileges for databases; EXECUTE privilege for functions and procedures; and USAGE privilege for languages and data types (including domains).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With