From: | Gevik Babakhani <pgdev(at)xs4all(dot)nl> |
---|---|
To: | pgsql-patches(at)postgresql(dot)org |
Subject: | Patch for %Allow per-database permissions to be set via GRANT |
Date: | 2006-04-26 14:23:25 |
Message-ID: | 1146061405.18394.17.camel@voyager.truesoftware.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-patches |
This patch implements the TODO Item: "%Allow per-database permissions to
be set via GRANT"
Implementation details:
1. A privilege ACL_CONNECT has been added to the ACL bits
2. The ACL_CONNECT can be recognized by character "c" in
pg_database/dataacl
3. The patch implements:
GRANT CONNECTION ON DATABASE mydatabase TO myuser
REVOKE CONNECTION ON DATABASE mydatabase FROM myuser
4. The initial condition ACL=NULL is treated as default
ACL=ACL_CREATE_TEMP | ACL_CONNECT providing backward compatibility with
the current pg_hba.conf
Notes:
As discussed :
A database owner WITHOUT SUPERUSER privileges can lock himself out from
connecting to his database. Try:
#psql -U user1 -d user1
Revoke connection on database user1 from public;
Revoke connection on database user1 from user1;
In this case no warning will be shown to the user informing he/she is
possibly locked out. This behavior is discussed in the hackers list.
The solution for a possible lockout would be to connect as a superuser
and GRANT CONNECTION ON DATABASE user1 TO <anyuser or public>
The implementation is best used for systems not wishing to change
pg_hba.conf frequently. In that case a simple host record can be added
to pg_hba.conf, providing from witch network the server is allowed to be
connected from and the database connection privilege can be granted or
revoked from withing SQL.
e.g.
CREATE USER user1 LOGIN;
CREATE USER user2 LOGIN;
CREATE DATABASE user1 OWNER user1;
REVOKE CONNECTION ON DATABASE user1 FROM PUBLIC;
GRANT CONNECTION,CREATE ON DATABASE user1 TO user2;
SELECT datname,datacl FROM pg_catalog.pg_database;
The patch can be downloaded from:
http://www.xs4all.nl/~gevik/patch/patch-0.7.diff
Many thanks to Tom Lane and Alvaro Herrera for their insight and
coaching.
Regards,
Gevik.
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2006-04-26 18:33:09 | Re: [HACKERS] Enhanced containment selectivity function |
Previous Message | Dhanaraj M | 2006-04-26 08:53:32 | Patch for BUG #2073: Can't drop sequence when created via SERIAL column |