Postgresql – Allow access to specific user only

permissionspostgresql

I have a database with an owner for it. My problem is that, though I have assigned a password for the owner, any other database user can access this database locally, even without a password.

How can I configure the database so that only a specific user has access to it (only with right password)?

Best Answer

You will have to edit your pg_hba.conf file. If you really want to reduce possible access to a single user (see the note below), you will need the following lines, and only these:

# TYPE  DATABASE  USER       ADDRESS   METHOD
host    all       dbowner    all       md5
local   all       dbowner              md5

This means that only dbowner will have access to any DB, logging in via the Unix socket or a TCP connection (from all addresses, including localhost). You can replace 'all' with your DB name if you want to limit access to a single DB.

After having edited the file, you will have to reload the config - depending on your system, do a pg_ctl reload or kill -HUP <postmaster_pid>, or do this form inside the DB, by calling

SELECT pg_reload_conf();

Please note that in the future you might want to do certain things that need rights dbowner may not have (be it SUPERUSER, CREATEUSER or something else). Depending on the policy you follow, you'll have to edit the file again to allow further access to a user (eg. postgres) that has the necessary privileges, or you have to assign these to dbowner before closing the door on other users.

Related Solutions

Postgresql – Created user can access all databases in PostgreSQL without any grants

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit: In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.

Sql-server – Changing db ownership using a user which can only see the sa

May not be pretty, but you could use a trigger on database creation that executes as a privileged user of your choice:

CREATE TRIGGER [database_setup]

    ON ALL SERVER
    WITH EXECUTE AS 'privuser'
    FOR CREATE_DATABASE
    AS
    BEGIN
    SET NOCOUNT ON
    DECLARE @db nvarchar(100)
    DECLARE @sql nvarchar(MAX)
    DECLARE @data XML
    SET @data = EVENTDATA()

    SELECT @db = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(100)');
    SELECT @sql = ('ALTER AUTHORIZATION ON DATABASE::' + @db + ' TO sa;');
    sp_executesql @sql;
    END
GO

ENABLE TRIGGER [database_setup] ON ALL SERVER
GO

Best Answer

Related Solutions

Postgresql – Created user can access all databases in PostgreSQL without any grants

Sql-server – Changing db ownership using a user which can only see the sa

Related Question