Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What does it mean when psql has blank database access privileges?

Tags:

postgresql

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)
like image 746
Buttons840 Avatar asked Feb 09 '17 21:02

Buttons840


People also ask

How do I check access privileges in PostgreSQL?

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.

What is privileges in Postgres?

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.

Can Postgres user access all databases?

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.


1 Answers

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).

like image 142
a_horse_with_no_name Avatar answered Sep 29 '22 12:09

a_horse_with_no_name