pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBest procedure for restricted access
3+ messages / 2 participants
[nested] [flat]
* Best procedure for restricted access
@ 2011-09-12 13:03 Rainer Leo <leo@workfile.de>
0 siblings, 0 replies; 3+ messages in thread
From: Rainer Leo @ 2011-09-12 13:03 UTC (permalink / raw)
To: pgsql-admin
Hello,
I have been asked to configure a database role
to be used for ODBC access.
So far I have done this:
CREATE ROLE odbc_user LOGIN
ENCRYPTED PASSWORD 'bar'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
REVOKE ALL ON DATABASE foo FROM odbc_user;
REVOKE CREATE ON SCHEMA public FROM public;
GRANT SELECT ON v_sales TO odbc_user;
When I try:
foo-> SELECT * FROM customers;
access is denied as expected
foo->\d baz
I see table definitions.
How can I restrict the role "odbc_user" to just
"SELECT whatever FROM v_sales;" and nothing else?
Any help would be very appreciated.
Regards,
Rainer Leo
workfile Datenbankservice
Bocksberg 20c
D-22395 Hamburg
Fon: 040.60 44 90 41
Fax: 040.34 92 61 08
www.workfile.de
^ permalink raw reply [nested|flat] 3+ messages in thread
* Best procedure for restricted access
@ 2011-09-12 13:16 Rainer Leo <leo@workfile.de>
0 siblings, 1 reply; 3+ messages in thread
From: Rainer Leo @ 2011-09-12 13:16 UTC (permalink / raw)
To: pgsql-admin
Hello,
I have been asked to configure a database role
to be used for ODBC access.
So far I have done this:
CREATE ROLE odbc_user LOGIN
ENCRYPTED PASSWORD 'bar'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
REVOKE ALL ON DATABASE foo FROM odbc_user;
REVOKE CREATE ON SCHEMA public FROM public;
GRANT SELECT ON v_sales TO odbc_user;
When I try:
foo-> SELECT * FROM customers;
access is denied as expected
foo->\d baz
I see table definitions.
How can I restrict the role "odbc_user" to just
"SELECT whatever FROM v_sales;" and nothing else?
Any help would be very appreciated.
Regards,
Rainer Leo
workfile Datenbankservice
Bocksberg 20c
D-22395 Hamburg
Fon: 040.60 44 90 41
Fax: 040.34 92 61 08
www.workfile.de
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best procedure for restricted access
@ 2011-09-12 15:26 Craig Ringer <ringerc@ringerc.id.au>
parent: Rainer Leo <leo@workfile.de>
0 siblings, 0 replies; 3+ messages in thread
From: Craig Ringer @ 2011-09-12 15:26 UTC (permalink / raw)
To: Rainer Leo <leo@workfile.de>; +Cc: pgsql-admin
On 12/09/2011 9:16 PM, Rainer Leo wrote:
> Hello,
>
> I have been asked to configure a database role
> to be used for ODBC access.
>
> So far I have done this:
>
> CREATE ROLE odbc_user LOGIN
> ENCRYPTED PASSWORD 'bar'
> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
>
> REVOKE ALL ON DATABASE foo FROM odbc_user;
> REVOKE CREATE ON SCHEMA public FROM public;
> GRANT SELECT ON v_sales TO odbc_user;
>
> When I try:
>
> foo-> SELECT * FROM customers;
> access is denied as expected
>
> foo->\d baz
> I see table definitions.
You'd have to mess with permissions on the pg_catalog tables and the
INFORMATION_SCHEMA views. This may have unexpected side-effects or cause
some clients that expect to be able to use those schema to get metadata
to cease functioning correctly.
I don't think denying access to table definitions is part of the
security model's goals at the moment; it's about limiting access to
_data_ not DDL or definitions. You'll note that function sources are
also available via pg_catalog, though it seems to be reasonably safe
(from what I hear, having not tested it) to change permissions to deny
access to those.
--
Craig Ringer
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-09-12 15:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-12 13:03 Best procedure for restricted access Rainer Leo <leo@workfile.de>
2011-09-12 13:16 Best procedure for restricted access Rainer Leo <leo@workfile.de>
2011-09-12 15:26 ` Craig Ringer <ringerc@ringerc.id.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox