Question 251129 in PostgreSQL Question posted in
The official documentation can be found here.

postgresql default permissions when creating databases

am new to postgres,

am trying to work out whats going on with security.

it seams that by default the system is wide open because when I create a new database every user can see it and seams to have full control.

how do I stop this from happening so that nobody other than postgre has access to it until a grant is issued ?

Tags:

2

Answers


  1. 0

    pg_default_acl contains the existing defailt privileges that determines what is granted to users by default when they create new objects like databases, schemas, tables etc.

    SELECT * FROM pg_default_acl;

    This will get you all your current default privileges that exist, if you need to change this such that you are restricting access to the new users, you need to alter this.

    So, if you want to revoke all privileges for all users on new schema you do something like this.

    ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM PUBLIC;

    Or, if you want to remove certain privileges, then you can use something like –

    REVOKE CREATE ON DATABASE dbname FROM PUBLIC;

    This will revoke the create privilege from the public role.

    Or, let’s say you want to grant some specific access to certain users, then you use the grant function –

    GRANT CONNECT, CREATE ON DATABASE dbname TO myuser;

    This way, poeple with explicit permissions will only have access to the database.

    Login or Signup to reply.
  2. 0

    You can restrict access to new databases by modifying the PostgreSQL pg_hba.conf file to limit access to specific roles or IP addresses. Additionally, use the REVOKE command to remove public access to new databases until explicit grants are provided.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search