Postgres revoke database access from user

Question

When I tried

REVOKE ALL PRIVILEGES ON DATABASE postgres from admin;

but the user admin is still able to connect to postgres remotely via pgadmin after that.

How do I completely revoke an user's access to a database?

/root$ psql -U postgres
psql (9.2.24)
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 admin     | No inheritance                                 | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

postgres=# REVOKE ALL PRIVILEGES ON DATABASE postgres from admin; 
REVOKE
postgres=# SELECT * FROM pg_stat_activity;
 datid | datname  |  pid   | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |
          query_start          |         state_change          | waiting | state  |              query              
-------+----------+--------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+
-------------------------------+-------------------------------+---------+--------+---------------------------------
 12924 | postgres | 121487 |       10 | postgres | psql             |             |                 |          -1 | 2018-03-10 20:20:42.458031+08 | 2018-03-10 20:21:27.367078+08 |
 2018-03-10 20:21:27.367078+08 | 2018-03-10 20:21:27.367082+08 | f       | active | SELECT * FROM pg_stat_activity;
(1 row)

postgres=# \q
/root$ psql -U admin postgres
psql (9.2.24)
Type "help" for help.

postgres=>

Possible duplicate of Revoke access to postgres database for a role — Sepehr GH
– Sepehr GH, Commented Mar 10, 2018 at 12:40
@SepehrGH the answers there still allows the user to see the existence of the database (and hence can attempt to connect). I want to hide the database completely from the user. — Avery235
– Avery235, Commented Mar 10, 2018 at 12:58

Eelke · Accepted Answer · 2018-03-10 12:48:42Z

42

You probably also need to do a

REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;

Each role is an implicit member of PUBLIC.

answered Mar 10, 2018 at 12:48

Eelke

22.3k5 gold badges57 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Avery235 Over a year ago

This does prevent user from connecting to the database! But it is still able to see the existence of the database (and hence can attempt to connect). Is there a way to hide the existence of the database completely from an user?

Eelke Over a year ago

Not within a single instance (you could run multiple instances). Why do you want to? (Obscurity is not security)

Avery235 Over a year ago

I just prefer not to let the user know about the existence of other DBs (this is possible in MySQL). I guess I'll have to run multiple instances then...

RBB Over a year ago

PUBLIC is a virtual role?

Graham Leggett Over a year ago

@Eelke While obscurity is definitely not security, obscurity does stop a naive dev asking for access to a database they shouldn't access, triggering naive managers to schedule expensive meetings to discuss why dev shouldn't have access.

Collectives™ on Stack Overflow

Postgres revoke database access from user

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related